能否直接将Parquet文件加载到Snowflake中?如果可以,该如何操作?
谢谢。
能否直接将Parquet文件加载到Snowflake中?如果可以,该如何操作?
谢谢。
copy into STAGE_SCHEMA.MY_PARQUET_LOADER
from (
select
$1
,metadata$filename as metadata_filename
,metadata$file_row_number as metadata_file_row_number
,current_timestamp() as load_timestamp
from
@S3STAGE/path/)
pattern = '.*.parquet'
file_format = (
TYPE = 'PARQUET'
SNAPPY_COMPRESSION = TRUE )
ON_ERROR = 'SKIP_FILE_1%'
purge= TRUE;
这个存在的位置:
create or replace TABLE MY_PARQUET_LOADER (
RAW VARIANT,
METADATA_FILENAME VARCHAR(16777216),
METADATA_FILE_ROW_NUMBER NUMBER(38,0),
LOAD_TIMESTAMP TIMESTAMP_LTZ(9)
) cluster by (METADATA_FILENAME);
是的,这可能是一个解决方案
create or replace TABLE MY_PARQUET_LOADER (
RAW VARIANT,
METADATA_FILENAME VARCHAR(16777216),
METADATA_FILE_ROW_NUMBER NUMBER(38,0),
LOAD_TIMESTAMP TIMESTAMP_LTZ(9)
) cluster by (METADATA_FILENAME);
我使用这两个 SQL 来创建并加载数据到表中,这可能会让你的生活更轻松。
首先:使用此 SQL 创建表格 SQL。
with cols as (
select COLUMN_NAME || ' ' || TYPE col
from table(
infer_schema(
location=>'@LANDING/myFile.parquet'
, file_format=>'LANDING.default_parquet'
)
)
),
temp as (
select 'create or replace table myTable (' col1
union
select listagg(col, ',') col1
from cols
union
select ') ' col1
)
select listagg(col1)
from temp
其次,使用此SQL语句创建副本并将数据加载到表中
with cols as (
select expression
from table(
infer_schema(
location=>'@LANDING/myFile.parquet'
, file_format=>'LANDING.default_parquet'
)
)
),
temp as (
select 'copy into myTable from ( select ' col1
union
select listagg(expression, ',') col1
from cols
union
select 'from @LANDING/myFile.parquet ) ' col1
)
select listagg(col1)
from temp