How to load data into snowflake table using internal stage in bulk load? What are its types?

How to load data into snowflake table using internal stage in bulk load? What are its types?

Advanced User Asked on July 17, 2019 in Snowflake Process Platform.
Add Comment
1 Answer(s)

In Diyotta tool, snowflake datapoint has load type called Bulk Import. In that load properties we will have one property named Internal Stage type which will have two options in dropdown as shown below.

 

RE: How to load data into snowflake table using internal stage in bulk load? What are its types?

When we select Bulk Import, by default, each user and table in Snowflake is automatically allocated an internal stage for staging data files to be loaded. In addition, you can create named internal stages.

File staging information is required during both steps in the data loading process:

You must specify an internal stage in the PUT command when uploading files to Snowflake

SQL – PUT ‘file:///I:/svn_root/disuite/branches/Enhancements_4_1_0_3117_000/Installer/diyotta/agent/app/stage/properties_26721_36128.csv’ @~/tmp/SRC_properties_26721_36128 PARALLEL=4 AUTO_COMPRESS=TRUE

 

  1. You must specify the same stage in the COPY INTO <table> command when loading data into a table from the staged files.

SQL – COPY INTO DEMO_TFORM.PUBLIC.T_SRC_PROPERTIES_26721_36128 (invoice_number,payment_method,invoice_date,net_amount_due,invoice_type,SCAC,date_current,settlement_option,currency_code,terms_type,terms_basis_date,terms_net_due_date,terms_net_days,invoice_reference,invoice_note,account_number,package_count,total_charged,sac_code,Parent_ID,GID_properties,JSON_ROW) FROM (SELECT $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22 FROM @~/tmp/SRC_properties_26721_36128) ON_ERROR=’ABORT_STATEMENT’ PURGE=FALSE RETURN_FAILED_ONLY=FALSE ENFORCE_LENGTH=TRUE TRUNCATECOLUMNS=FALSE FORCE=TRUE LOAD_UNCERTAIN_FILES=FALSE FILE_FORMAT=( type=’CSV’ COMPRESSION=’AUTO’ RECORD_DELIMITER=’\\n’ ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE FIELD_DELIMITER=” TIMESTAMP_FORMAT=’AUTO’ BINARY_FORMAT=’UTF-8′ NULL_IF=’NULL’ EMPTY_FIELD_AS_NULL=FALSE);remove @~/tmp/SRC_properties_26721_36128;

 

There will be two types of stages,

  1. User stage
  2. Table stage

User Stage:

Each user has a Snowflake stage allocated to them by default for storing files. This stage is a convenient option if your files will only be accessed by a single user but need to be copied into multiple tables.

User stages have the following characteristics and limitations:

  • User stages are referenced using @~; e.g. use LIST @~ to list the files in a user stage.
  • Unlike named stages, user stages cannot be altered or dropped.
  • User stages do not support setting file format options. Instead, you must specify file format and copy options as part of the COPY INTO <table> command.

Note**:

This option is not appropriate if:

  • Multiple users require access to the files.
  • The current user does not have INSERT privileges on the tables the data will be loaded into.

 

Table Stage:

Each table has a Snowflake stage allocated to it by default for storing files. This stage is a convenient option if your files need to be accessible to multiple users and only need to be copied into a single table.

Table stages have the following characteristics and limitations:

  • Table stages have the same name as the table; e.g. a table named mytable has a stage referenced as @%mytable.

SQL – PUT ‘file:///I:/svn_root/disuite/branches/Enhancements_4_1_0_3117_000/Installer/diyotta/agent/app/stage/properties_26721_36128.csv’ @%sps_tfg_carrier_invoice_upd/tmp/SRC_properties_26721_36128 PARALLEL=4 AUTO_COMPRESS=TRUE

 

  • Unlike named stages, table stages cannot be altered or dropped.
  • Table stages do not support setting file format options. Instead, you must specify file format and copy options as part of the COPY INTO <table> command.

SQL – COPY INTO DEMO_TFORM.PUBLIC.T_SRC_PROPERTIES_26721_36128 (invoice_number,payment_method,invoice_date,net_amount_due,invoice_type,SCAC,date_current,settlement_option,currency_code,terms_type,terms_basis_date,terms_net_due_date,terms_net_days,invoice_reference,invoice_note,account_number,package_count,total_charged,sac_code,Parent_ID,GID_properties,JSON_ROW) FROM (SELECT $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22 FROM @%sps_tfg_carrier_invoice_upd/tmp/SRC_properties_26721_36128) ON_ERROR=’ABORT_STATEMENT’ PURGE=FALSE RETURN_FAILED_ONLY=FALSE ENFORCE_LENGTH=TRUE TRUNCATECOLUMNS=FALSE FORCE=TRUE LOAD_UNCERTAIN_FILES=FALSE FILE_FORMAT=( type=’CSV’ COMPRESSION=’AUTO’ RECORD_DELIMITER=’\\n’ ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE FIELD_DELIMITER=” TIMESTAMP_FORMAT=’AUTO’ BINARY_FORMAT=’UTF-8′ NULL_IF=’NULL’ EMPTY_FIELD_AS_NULL=FALSE ) ;remove @%sps_tfg_carrier_invoice_upd/tmp/SRC_properties_26721_36128;

 

  • Table stages do not support transforming data while loading it (i.e. using a query as the source for the COPY command).

Note that a table stage is not a separate database object; rather, it is an implicit stage tied to the table itself. A table stage has no grantable privileges of its own. To stage files to a table stage, list the files, query them on the stage, or drop them, you must be the table owner (have the role with the OWNERSHIP privilege on the table).

Note**:

This option is not appropriate if you need to copy the data in the files into multiple tables.

 

Expert Answered on July 17, 2019.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.