英文:
How can I pass multiple variables as URI paths in a BQ stored procedure?
问题
我是BQ的新手，试图从GCS的多个子文件夹中加载多个csv文件到临时表中，并需要将变量作为uri中的路径。在uri部分出现错误，所以排除了加载脚本。
**查询错误：** 发现不支持的函数调用 'ARRAY[...]'; 无法设置OPTIONS()中的 'uris'。
**代码片段**
DECLARE my_bucket_path STRING;
DECLARE sub_dir_1 STRING;
DECLARE sub_dir_2 STRING;
DECLARE sub_dir_3 STRING;
SET my_bucket_path = 'gs://my_bucket';
SET sub_dir_1 = 'sub_dir_val1';
SET sub_dir_2 = 'sub_dir_val2';
SET sub_dir_3 = 'sub_dir_val3';
-- 创建引用GCS Bucket的外部数据源
CREATE OR REPLACE EXTERNAL TABLE mydataset.temp_table (
col1 STRING,
col2 FLOAT64,
col3 FLOAT64,
col4 INT64,
) OPTIONS (
format = 'csv',
uris = [my_bucket_path||'/'||sub_dir_1||'/'||sub_dir_2||'/'||sub_dir_3||'/*.csv'],
skip_leading_rows = 1);
英文:
I am new to BQ and trying to load multiple csv files to temp table in BQ from multiple subfolders from GCS, and need to use variable as a path in uri. Excluded the load script since getting error in uri's part.
Query error: Found unsupported function call 'ARRAY[...]'; failed to set 'uris' in OPTIONS()
Code Snippet
DECLARE my_bucket_path STRING;
DECLARE sub_dir_1 STRING;
DECLARE sub_dir_2 STRING;
DECLARE sub_dir_3 STRING;
SET my_bucket_path = 'gs://my_bucket';
SET sub_dir_1 = 'sub_dir_val1';
SET sub_dir_2 = 'sub_dir_val2';
SET sub_dir_3 = 'sub_dir_val3';
--Create external data source referencing the GCS Bucket
CREATE OR REPLACE EXTERNAL TABLE mydataset.temp_table (
col1 STRING,
col2 FLOAT64,
col3 FLOAT64,
col4 INT64,
) OPTIONS (
format = 'csv',
uris = [my_bucket_path||'/'||sub_dir_1||'/'||sub_dir_2||'/'||sub_dir_3||'/*.csv'],
skip_leading_rows = 1);
答案1
得分: 1
BigQuery不支持在CREATE EXTERNAL TABLE语句的uris选项中直接连接字符串。但是，您可以使用EXECUTE IMMEDIATE语句通过动态URI路径来实现这一点：
DECLARE my_bucket_path STRING;
DECLARE sub_dir_1 STRING;
DECLARE sub_dir_2 STRING;
DECLARE sub_dir_3 STRING;
SET my_bucket_path = 'gs://my_bucket';
SET sub_dir_1 = 'sub_dir_val1';
SET sub_dir_2 = 'sub_dir_val2';
SET sub_dir_3 = 'sub_dir_val3';
-- 创建动态URI路径
DECLARE uri_path STRING;
SET uri_path = CONCAT(my_bucket_path, '/', sub_dir_1, '/', sub_dir_2, '/', sub_dir_3, '/*.csv');
-- 创建用于创建外部表的SQL字符串
DECLARE create_table_sql STRING;
SET create_table_sql = CONCAT('
CREATE OR REPLACE EXTERNAL TABLE mydataset.temp_table (
col1 STRING,
col2 FLOAT64,
col3 FLOAT64,
col4 INT64
) OPTIONS (
format = "csv",
uris = ["', uri_path, '"],
skip_leading_rows = 1
)');
-- 执行SQL字符串
EXECUTE IMMEDIATE create_table_sql;
我们首先通过连接字符串来创建了一个uri_path变量。然后，我们创建了一个create_table_sql变量，其中包含了用于创建外部表的SQL语句，其中包含了动态URI路径。最后，我们使用EXECUTE IMMEDIATE语句来执行SQL字符串并创建表格。
英文:
BigQuery doesn't support direct concatenation of strings in the uris option of the CREATE EXTERNAL TABLE statement. However, you can achieve this by using the EXECUTE IMMEDIATE statement to create the table with a dynamic URI path:
DECLARE my_bucket_path STRING;
DECLARE sub_dir_1 STRING;
DECLARE sub_dir_2 STRING;
DECLARE sub_dir_3 STRING;
SET my_bucket_path = 'gs://my_bucket';
SET sub_dir_1 = 'sub_dir_val1';
SET sub_dir_2 = 'sub_dir_val2';
SET sub_dir_3 = 'sub_dir_val3';
-- Create the dynamic URI path
DECLARE uri_path STRING;
SET uri_path = CONCAT(my_bucket_path, '/', sub_dir_1, '/', sub_dir_2, '/', sub_dir_3, '/*.csv');
-- Create a SQL string for creating the external table
DECLARE create_table_sql STRING;
SET create_table_sql = CONCAT('
CREATE OR REPLACE EXTERNAL TABLE mydataset.temp_table (
col1 STRING,
col2 FLOAT64,
col3 FLOAT64,
col4 INT64
) OPTIONS (
format = "csv",
uris = ["', uri_path, '"],
skip_leading_rows = 1
)');
-- Execute the SQL string
EXECUTE IMMEDIATE create_table_sql;
We first create a uri_path variable by concatenating the strings. Then, we create a create_table_sql variable that contains the SQL statement to create the external table with the dynamic URI path. Finally, we use the EXECUTE IMMEDIATE statement to execute the SQL string and create the table.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论