从 parquet 文件创建 SQL 表

我正在使用 R 来处理大型数据集（最大数据帧 30.000.000 x 120）。这些文件作为 parquet 文件存储在 Azure Datalake 存储中，我们需要每天查询这些文件并将其还原到本地 SQL 数据库中。无需将数据加载到内存中即可读取 Parquet 文件，这很方便。然而，从 parqueet 文件创建 SQL 表更具挑战性，因为我不想将数据加载到内存中。

这是我使用的代码。不幸的是，这不是一个完美的表示，因为需要存在 SQL 数据库才能使其工作。

# load packages
library(tidyverse)
library(arrow)
library(sparklyr)
library(DBI)

# Create test data
test <- data.frame(matrix(rnorm(20), nrow=10))

# Save as parquet file
write_parquet(test2, tempfile(fileext = ".parquet"))


# Load main table
sc <- spark_connect(master = "local", spark_home = spark_home_dir())
test <- spark_read_parquet(sc, name = "test_main", path = "/tmp/RtmpeJBgyB/file2b5f4764e153.parquet", memory = FALSE, overwrite = TRUE)

# Save into SQL table
DBI::dbWriteTable(conn = connection,
                  name = DBI::Id(schema = "schema", table = "table"), 
                  value = test)

是否可以在不将 parquet 文件加载到内存的情况下编写 SQL 表？

我缺乏经验T-sql批量导入和导出，但这很可能是您找到答案的地方。

library(arrow)
library(DBI)
test <- data.frame(matrix(rnorm(20), nrow=10))
f <- tempfile(fileext = '.parquet')
write_parquet(test2, f)

#Upload table using bulk insert
dbExecute(connection, 
  paste("
    BULK INSERT [database].[schema].[table]
    FROM '", gsub('\\\\', '/', f), "' FORMAT = 'PARQUET';
  ")
)

我在这里使用T-sql's own bulk insert https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15命令。
免责声明我还没有使用过这个命令T-sql，所以它可能充满错误。例如，我在文档中看不到指定快速压缩的地方，尽管如果使用以下命令定义自定义文件格式，则可以指定它CREATE EXTERNAL FILE FORMAT https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver15.

现在上面的内容只插入到现有的表中。对于您的具体情况，您想从文件创建一个新表，您可能会寻找更多OPENROWSET https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver15 using CREATE TABLE AS [select statement] https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15.

column_definition <- paste(names(column_defs), column_defs, collapse = ',')
dbExecute(connection, 
paste0("CREATE TABLE MySqlTable
AS 
SELECT *
FROM 
  OPENROWSET(
    BULK '", f, "' FORMAT = 'PARQUET'
  ) WITH (
    ", paste0([Column definitions], ..., collapse = ', '), "
  );
")

where column_defs将是一个named描述为每列提供 SQL 数据类型定义的列表或向量。从 R 数据类型到（或多或少）完整的转换可在T-sql 文档页面 https://learn.microsoft.com/en-us/sql/machine-learning/r/r-libraries-and-data-types?view=sql-server-ver15（注意两个非常必要的翻译：Date and POSIXlt不存在）。再次免责声明：我在 T-sql 中的时间没有达到 BULK INSERT 或类似的。