英文:
Snowflake COPY INTO specific columns - How to do it
问题
To use the COPY INTO command to insert data into specific columns of a table, you can modify your query as follows:
COPY INTO tblUser (myNo, myName, myAddress, myStatus, dateTimeStamp, InsertedBy)
FROM (
SELECT
json_data:myNo AS myNo,
json_data:"myName"::VARCHAR AS myName,
json_data:"myAddress"::VARCHAR AS myAddress,
json_data:"myStatus"::VARCHAR AS myStatus,
current_timestamp AS dateTimeStamp,
current_user AS InsertedBy
FROM (
SELECT PARSE_JSON($1) AS json_data
FROM @STAGE_BASE/SUBDIR/sample_data_file.json (FILE_FORMAT => JSON_FORMAT)
)
);
This query specifies the columns you want to insert data into in the target table tblUser and maps them to the corresponding columns in your JSON data. It also uses aliases for column names in the subquery to ensure they match the table columns.
Make sure to replace " with double quotes (") in your actual code.
英文:
How to use COPY INTO command to insert data to specific columns of a table
table name: tblUser
columns & type:
myNo VARCHAR
myName VARCHAR
myAddress VARCHAR
myStatus VARCHAR
dateTimeStamp DATETIME
InsertedBy VARCHAR
I have a working query:
SELECT json_data:myNo
,json_data:"myName"::VARCHAR
,json_data:"myAddress"::VARCHAR
,json_data:"myStatus"::VARCHAR
,current_timestamp
,current_user
FROM(
SELECT PARSE_JSON($1) AS json_data
FROM @STAGE_BASE/SUBDIR/sample_data_file.json
(FILE_FORMAT => JSON_FORMAT)
);
I cannot make it work for some reason.
I have tried the copy into with columns...followed by the query...
I am getting some syntax wrong perhaps as it complains about the SELECT keyword.
What correction is needed?
答案1
得分: 1
COPY INTO table 支持两种类型：
/* 标准数据加载 */
COPY INTO [<namespace>.]<table_name> FROM { internalStage | externalStage | externalLocation } [ FILES = ( '<file_name>' [ , '<file_name>' ] [ , ... ] ) ] [ PATTERN = '<regex_pattern>' ] [ FILE_FORMAT = ( { FORMAT_NAME = '[<namespace>.]<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ] [ copyOptions ] [ VALIDATION_MODE = RETURN_<n>_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS ]
/* 数据加载及转换 */
COPY INTO [<namespace>.]<table_name> [ ( <col_name> [ , <col_name> ... ] ) ] FROM ( SELECT [<alias>.]$<file_col_num>[.<element>] [ , [<alias>.]$<file_col_num>[.<element>] ... ] FROM { internalStage | externalStage } ) [ FILES = ( '<file_name>' [ , '<file_name>' ] [ , ... ] ) ] [ PATTERN = '<regex_pattern>' ] [ FILE_FORMAT = ( { FORMAT_NAME = '[<namespace>.]<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ] [ copyOptions ]
Transforming Data During a Load
>
> COPY 命令支持：
> - 列重排列、列省略和使用 SELECT 语句进行强制类型转换。您的数据文件无需具有与目标表相同数量和顺序的列。
> - ENFORCE_LENGTH | TRUNCATECOLUMNS 选项，可以截断超过目标列长度的文本字符串。
示例：
Reorder CSV Columns During a Load
copy into home_sales(city, zip, sale_date, price) from (select SUBSTR(t.$2,4), t.$1, t.$5, t.$4 from @mystage t) FILE_FORMAT = (FORMAT_NAME = mycsvformat);
英文:
COPY INTO table supports two types:
> /* Standard data load */
>
> COPY INTO [<namespace>.]<table_name>
> FROM { internalStage | externalStage | externalLocation }
> [ FILES = ( '<file_name>' [ , '<file_name>' ] [ , ... ] ) ]
> [ PATTERN = '<regex_pattern>' ]
> [ FILE_FORMAT = ( { FORMAT_NAME = '[<namespace>.]<file_format_name>' |
> TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
> [ copyOptions ]
> [ VALIDATION_MODE = RETURN_<n>_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS ]
> /* Data load with transformation */
>
> COPY INTO [<namespace>.]<table_name> [ ( <col_name> [ , <col_name> ... ] ) ]
> FROM ( SELECT [<alias>.]$<file_col_num>[.<element>] [ , [<alias>.]$<file_col_num>[.<element>] ... ]
> FROM { internalStage | externalStage } )
> [ FILES = ( '<file_name>' [ , '<file_name>' ] [ , ... ] ) ]
> [ PATTERN = '<regex_pattern>' ]
> [ FILE_FORMAT = ( { FORMAT_NAME = '[<namespace>.]<file_format_name>' |
> TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
> [ copyOptions ]
Transforming Data During a Load
>
> The COPY command supports:
> - Column reordering, column omission, and casts using a SELECT statement. There is no requirement for your data files to have the same number and ordering of columns as your target table.
> - The ENFORCE_LENGTH | TRUNCATECOLUMNS option, which can truncate text strings that exceed the target column length.
Example:
Reorder CSV Columns During a Load
> copy into home_sales(city, zip, sale_date, price)
> from (select SUBSTR(t.$2,4), t.$1, t.$5, t.$4 from @mystage t)
> FILE_FORMAT = (FORMAT_NAME = mycsvformat);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论