This article describes how to load Tealium data into Snowflake using the Snowflake web interface.
This article focuses on the Snowflake web interface and Data Load wizard and provides an overview of Snowflake command line tools. For more detailed information about using Snowflake, see the Snowflake Documentation.
In this article:
Tealium DataAccess customers can use the Snowflake web interface to connect their data to an external solution for advanced querying and data analysis. The web interface provides an intuitive wizard that you can use to load limited amounts of data into a table from a small set of flat files. To do this, the wizard uses the PUT and COPY commands to load data and then simplifies the data loading process by combining the staging files and loading data phases into one single operation.
The web interface is intended for loading small numbers of files (up to 50 MB). This limitation ensures optimal performance due to the fact that browser performance can vary between hardware, browser types, and browser versions.
The following items are required to connect your DataAccess data from the Tealium-issued Amazon S3 bucket:
The following sections guide you through the steps required use the Snowflake Data Load web interface to select source files and load Tealium data into Snowflake.
Tealium recommends loading DataAccess data to a table with a variant data type, which accommodates the semi-structured nature of JSON files without enforcing schema requirements. See the Best Practices section in this document for additional information and tips on parsing the data after it has loaded.
Use the following steps to open the Snowflake Data Load wizard:
When prompted to select source files, you will create a new stage for your Tealium DataAccess data that resides in AWS S3.
Use the following steps to select source files from Tealium DataAccess:
Use the following steps to enter your DataAccess credentials:
If your credentials have already been generated, your Secret Access Key does not display. Work with your colleagues to retrieve the existing key or to determine if a new one should be regenerated.
Use the following steps to create a file format:
Use the following steps to continue manually loading data using the web interface, continue reading the steps below. You can optionally load data from the command line. If you select this method, skip to the Load Using Command Line section.
If the warehouse is not currently running, resuming the warehouse could take up to 5 minutes, in addition to the time required for loading.
Use the following steps to load your data from the command line:
[connections.tealium_example] #Can be used in SnowSql as connect tealium_example accountname = MY_ACCOUNT_NAME username = MYUSER password = ********************
tealium_examplewith your connection name:
$ snowsql -c tealium_example
COPY INTO <table>command to load your data into the target table. The following example loads data from files in the named stage
my_dataaccess_stagewhich was created in the previous steps.
COPY INTO mytable FROM ‘@my_dataaccess_stage/events/’ FILE_FORMAT ‘TEALIUM_JSON’;
This section provides helpful information about best practices for parsing data after the data is loaded.
EventStore is formatted in a flattened JSON file. If you followed the data loading steps described in this document, your EventStore data should now be structured in your variant type column. In order to format the data into a more usable structure for querying, you must parse the JSON.
The following sample query defines all default EventStore attributes columns into dedicated columns of a database view:
CREATE OR REPLACE VIEW TEALIUM_STD_EVENTS AS SELECT JSON:"visitorid"::string as visitorid , JSON:"eventid"::string as eventid , JSON:"useragent"::string as useragent , JSON:"device_type"::string as device_type , JSON:"eventtime"::number as eventtime , JSON:"dom_domain"::string as dom_domain , JSON:"dom_pathname"::string as dom_pathname , JSON:"dom_query_string"::string as dom_query_string , JSON:"dom_title"::string as dom_title , JSON:"dom_url"::string as dom_url , JSON:"dom_viewport_height"::number as dom_viewport_height , JSON:"dom_viewport_width"::number as dom_viewport_width , JSON:"pageurl_domain"::string as pageurl_domain , JSON:"pageurl_full_url"::string as pageurl_full_url , JSON:"pageurl_path"::string as pageurl_path , JSON:"pageurl_querystring"::string as pageurl_querystring , JSON:"pageurl_scheme"::string as pageurl_scheme , JSON:"referrerurl_domain"::string as referrerurl_domain , JSON:"referrerurl_full_url"::string as referrerurl_full_url , JSON:"referrerurl_path"::string as referrerurl_path , JSON:"referrerurl_querystring"::string as referrerurl_querystring , JSON:"referrerurl_scheme"::string as referrerurl_scheme , JSON:"udo_ut_account"::string as udo_ut_account , JSON:"udo_ut_domain"::string as udo_ut_domain , JSON:"udo_ut_env"::string as udo_ut_env , JSON:"udo_ut_event"::string as udo_ut_event , JSON:"udo_ut_profile"::string as udo_ut_profile , JSON:"udo_ut_version"::string as udo_ut_version , JSON:"firstpartycookies_utag_main__pn"::number as cookie_utag_main__page_number , JSON:"firstpartycookies_utag_main__sn"::number as cookie_utag_main__session_number , JSON:"firstpartycookies_utag_main__ss"::number as cookie_utag_main__session_start_flag , JSON:"firstpartycookies_utag_main__st"::number as cookie_utag_main__sessoin_timeout , JSON:"firstpartycookies_utag_main_ses_id"::number as cookie_utag_main__session_id , JSON:"udo_timing_pathname"::string as timing_pathname , JSON:"udo_timing_dns"::number as timing_dns , JSON:"udo_timing_load"::number as timing_load , JSON:"udo_timing_connect"::number as timing_connect , JSON:"udo_timing_response"::number as timing_response , JSON:"udo_timing_dom_interactive_to_complete"::number as timing_dom_interactive_to_complete , JSON:"udo_timing_front_end"::number as timing_front_end , JSON:"udo_timing_dom_loading_to_interactive"::number as timing_dom_loading_to_interactive , JSON:"udo_timing_query_string"::string as timing_query_string , JSON:"udo_timing_domain"::string as timing_domain , JSON:"udo_timing_fetch_to_interactive":: as timing_fetch_to_interactive , JSON:"udo_timing_timestamp"::number as timing_timestamp , JSON:"udo_timing_fetch_to_complete"::number as timing_fetch_to_complete , JSON:"udo_timing_fetch_to_response"::number as timing_fetch_to_response , JSON:"udo_timing_time_to_first_byte"::number as timing_time_to_first_byte FROM EVENTSTORE_SAMPLE;
For detailed information about these fields, see the EventStore Data Guide.
AudienceStore data can have several variations, each depending on the configuration of the connector actions. In the previous setup steps, you defined your file format for the Snowflake stage as JSON.
You can use the following sample query to create a view containing all default visitor badges, dates, strings, numbers and Booleans.
CREATE OR REPLACE VIEW TEALIUM_STD_VISITORS AS
JSON:_id::string as "visitor_id"
, JSON:metrics."Average visit duration in minutes"::number as "avg_visit_duration"
, JSON:metrics."Average visits per week"::number as "avg_visits_per_week"
, JSON:metrics."Lifetime event count"::number as "ltm_event_count"
, JSON:metrics."Lifetime visit count"::number as "ltm_visit_count"
, JSON:metrics."Total direct visits"::number as "total_direct_visits"
, JSON:metrics."Total referred visits"::number as "total_referred_visits"
, JSON:metrics."Total time spent on site in minutes"::number as "total_time_onsite"
, JSON:metrics."Weeks since first visit"::number as "weeks_since_first_visit"
, JSON:dates."First visit"::number as "first_visit_date"
, JSON:dates."Last visit"::number as "last_visit_date"
, JSON:properties."Last event url"::string as "last_event_url"
, JSON:properties."Lifetime browser types used (favorite)"::string as "ltm_browser_types_used_fav"
, JSON:properties."Lifetime browser versions used (favorite)"::string as "ltm_browser_versions_used_fav"
, JSON:properties."Lifetime devices used (favorite)"::string as "ltm_devices_used_fav"
, JSON:properties."Lifetime operating systems used (favorite)"::string as "ltm_os_used_fav"
, JSON:properties."Lifetime platforms used (favorite)"::string as "ltm_platforms_used_fav"
, JSON:badges."Fan"::boolean as "fan_badge"
, JSON:badges."Frequent visitor"::boolean as "frequent_visitor_badge"
, JSON:badges."Unbadged"::boolean as "unbadged"
, ."Returning visitor"::boolean as "returning_visitor_flag"
For more advanced parsing of the visitor profile object, see the Snowflake Semi-Structured Data documentation.
See the following resources for additional information: