CREATING A SNOWFLAKE DESTINATION
- In the Transfer Element, click +Add in the top right.
- From the list, select the Snowflake Destination.
- Optional: If you don't yet have a Connection to your Snowflake database, you will be prompted to create one. Input your Snowflake credentials and Hostname to authorize Adverity to write to your Snowflake database. Hostname should be written in the format:
Note: If your region is US-WEST, then region_id is not required in the hostname:
- Select the relevant Warehouse, Database, and Schema from the drop-down lists of available options.
- Optional: Create Table Mapping entries if required.
- Assign the new Destination to the desired Workspace.
- The Destination is now set up, and can be connected to any of the Datastreams within the assigned Workspace.
Maximum field character length for import is 16k (when single-byte). For further information, please see Snowflake's Documentation.
Adverity will always export JSON fields (VARIANT) as as text strings (VARCHAR). These strings can then be transformed back to JSON via the select SQL query in Snowflake...
select get_path(PARSE_JSON(json_field), 'field') as parsed_json from DEV_FTP_58
...where the PARSE_JSON function transforms the text field of the table into JSON and get_path looks for the value of 'field' in the JSON. Please see Snowflake's Documentation for further information.
During a data transfer to a database Destination, a "_stage" table will be created in your database during processing. Once transfer is complete, this table will be replaced with a final version lacking the "_stage" prefix. If an error occurs mid-transfer that requires the process be aborted, the "_stage" table will remain in your Destination database, but it will only contain data transferred until the point that the error was encountered.
You must investigate and resolve the Error before retrying the transfer.
LOAD STATE ERRORS
If the following issue occurs on import...
"Cannot set load state due to error: (snowflake.connector.errors.ProgrammingError) 390114 (08001): Authentication token has expired. The user must authenticate again.'"
...please contact our Support Team. These issues are rare, occurring only on extremely lengthy data loads, but require direct intervention to resolve.