Data Loading & Unloading Flashcards
(98 cards)
1
Q
Stages
A
Stages are temporary storage locations for data files used in the data loading and unloading process
- Broken into Internal and External
2
Q
Internal Stages are divided into what 3 stages?
A
- User stage
- Table stage
- Named stage
3
Q
User Stage
A
- An internal stage
- Automatically allocated when a user is created
- Use a PUT command to get a file from your local machine to Snowflake
- reference to a user stage is: ls@~;
- Cannot be altered or dropped
- Not appropriate if multiple users need access to stage
4
Q
Table Stage
A
- An internal stage
- Automatically allocated when a table is created
- Use a PUT command to get the file into a table stage
- Reference to a table stage is: ls @%MY_TABLE;
- Cannot be altered or dropped
- User must have ownership privileges on table
5
Q
Named Stage
A
- An internal stage
- User created database object
- Use a PUT command to get the file into a name stage
- Reference to a named stage is: ls@MY_STAGE;
- Securable object
6
Q
In internal storage, uncompressed files are automatically compressed using ________ when loaded into an internal storage unless explicitly set not to.
A
GZIP
7
Q
In internal storage, stage files are automatically encrypted using ____ bit keys.
A
128
8
Q
External Stages
A
External stages reference data files stored in a location outside of Snowflake, which we manage ourselves.
- Could be Amazon S3 buckets, Google cloud storage buckets, or Microsoft Azure containers
9
Q
External named stage
A
- User created database object
- Files are uploaded using the cloud utilities of the cloud provider
- Reference to a named stage is ls @MY_STAGE;
- Storage locations can be private or public
- Copy options such as ON_ERROR and PURGE can be set on stages
10
Q
Storage Integration
A
A storage integration is a reusable and securable Snowflake object which can be applied across stages and is recommended to avoid having to explicitly set sensitive information for each stage definition.
11
Q
Stage Helper Command - LIST
A
List the contents of a stage:
- Path of staged file
- Size of staged file
- MD5 Hash of staged file
- Last updated timestamp
- Can optionally specify a path for specific folders or files
- Named and internal table stages can optionally include database and schema global pointer
12
Q
Stage Helper Command - SELECT
A
- Query the contents of staged files directly using standard SQL for both internal and external stages
- Useful for inspected files prior to data loading/unloading
- Reference metadata columns such as filename and row numbers for a staged file
13
Q
Stage Helper Command - REMOVE
A
- Remove files from either an external or internal stage
- Can optionally specify a path for specific folders or files
- Named and internal table stages can optionally include database and schema global pointer
14
Q
PUT command
A
- The PUT command uploads data files from a local directory on a client machine to any of the three types of internal stage.
- Uploaded files are automatically encrypted with a 128-bit key with optional support for a 256-bit key.
15
Q
PUT cannot be executed from within ____________.
A
worksheets
16
Q
Duplicate files uploaded to a stage via PUT are _______.
A
ignored
17
Q
COPY INTO <table> statement
A
- The COPY INTO <table> statement copies the contents of an internal or external location directly into a table
- COPY INTO <table> requires a user created virtual warehouse to execute
- Load history is stored in the metadata of the target table for 64 days, which ensures files are not loaded twice
18
Q
What file formats can be uploaded into Snowflake?
A
- Delimited files (CSV, TSC, etc)
- JSON
- Avro
- ORC
- Parquet
- XML
19
Q
Copy Option: ON_ERROR
A
Value that specifies the error handling for the load operation:
- CONTINUE
- SKIP_FILE
- SKIP_FILE_<num>
- SKIP_FILE_<num>%
- ABORT_STATEMENT</num></num>
Default Value - ‘ABORT_STATEMENT’
20
Q
Copy Option: SIZE_LIMIT
A
Number that specifies the maximum size of data loaded by a COPY statement
Default - null(no size limit)
21
Q
Copy Option: PURGE
A
Boolean that specifies whether to remove the data files from the stage automatically after the data is loaded successfully
Default Value - FALSE
22
Q
Copy Option: RETURN_FAILED_ONLY
A
Boolean that specifies whether to return only files that have failed to load in the statement result
Default Value - FALSE
23
Q
Copy Option: MATCH_BY_COLUMN_NAME
A
String that specifies whether to load semi-structured data into columns in the target table that match corresponding columns represented in the data
Default Value - None
24
Q
Copy Option: ENFORCE_LENGTH
A
Boolean that specifies whether to truncate text strings that exceed the target column length
Default Value - TRUE
25
Copy Option: TRUNCATECOLUMNS
Boolean that specifies whether to truncate text strings that exceed the target column length
Default Value - FALSE
26
Copy Option: FORCE
Boolean that specifies to load all files, regardless of whether they've been loaded previously and have not changed since they were loaded
Default Value - FALSE
27
Copy Option: LOAD_UNCERTAIN_FILES
Boolean that specifies to load files for which the load status is unknown. The COPY command skips these files by default.
Default Value - FALSE
28
Output Column Names: COPY INTO
