Google Analytics data can be made available in BigQuery after submitting a BigQuery export request to Google. The data is provided in a session-based nested structure and stored in sharded tables. The Syntasa Google Analytics input adapter provides the ability to ingest the sharded and nested structure style of GA data that resides in BigQuery, flattening the data by unnesting, piecing the shards together, and providing the ability to enrich with other types of data (i.e. sales, CRM). Prepared data is written to Google Cloud Storage (GCS) and can also be written back to BigQuery for analytical purposes.
The purpose of this article is to provide a step-by-step configuration of the Google Analytics Input Adapter.
Contents
- Common Acronyms
- Pre-Requisites
- Google Analytics App Creation
- Initial Configuration of the Google Analytics Input Adapter App
- Syntasa Development & Production Concept
- Output
- Additional Configurations
Common Acronyms
- AA - Adobe Analytics
- AAM - Adobe Audience Manager
- AWS - Amazon Web Service
- BQ - BigQuery
- GCP - Google Cloud Platform
- GCS - Google Cloud Storage
- S3 - Amazon Simple Storage Service
- SFTP - Secure File Transfer Protocol
- UI - User Interface
Pre-Requisites
As with most other apps, there are requirements for ensuring that data can be extracted, processed and written to the desired database and storage location(s). The following must be completed before setting up the adapter:
- Google BigQuery Export for Analytics configured.
- Infrastructure screen populated.
- Connections defined for Adobe API and raw source files.
- Event Store created.
Google Analytics App Creation
- Click on the menu icon (
) and under Apps select "Synthesizer" from the sub-menu. - Create a new app by clicking on the plus icon (
) on the far right of the screen. - A new "New App" modal will appear.
- Fill in the fields:
- Name - Descriptive, concise, and unique name of the app, e.g. Web Global Data.
- Key - This typically will auto-populate and is good to leave as-is.
- App Prefix - Provides a short string of text that will make it easy for the users to discern database tables, e.g. aam.
- Template - Select "Google Analytics Adapter".
- Description (Optional) - Provide a lengthier detailed description of the purpose of the app.
- Data Store - Select a relevant Data Store you'd like the data written. Our best practice is to create a "Synthesizer Event Store" that is used for all Synthesizer apps.
- Default Partition Scheme - Select "Daily".
- Override Icon (Optional) - Toggle if you'd like to upload your own image to display on the app tiles screen.
- Click the green "CREATE" button.
- The app will appear on the app tiles screen.
Initial Configuration of the Google Analytics Input Adapter App
For Synthesizer apps such as the GA Input Adapter app, the source data will typically come from sharded BigQuery tables. The data will be prepared in a manner that facilitates user access via BigQuery.
The focus of this section will the preparation of the five processes that make up the GA Input Adapter template.
Add a Connection
Connections provide the required information for the app to understanding where the source files are sitting:
- Locate the newly created app and click to enter the app workflow.
- Click the (
) icon to unlock the app workflow. - Drag a "Connection" that relates to the connection created in the pre-requisite steps.
- Click the "Connection" node and select the connection name where the raw source files reside.
- Click the green and white checkmark (
) to accept changes. - Connect the "Connection" node to the "From BQ" node.
- The "From BQ" node should turn white, click the "From BQ" node.
From BQ
The From BQ process provides the ability to pull in the files from the specified BigQuery location.
- Click the "From BQ" process.
- Configure 'Input' screen - Provides details of where the raw data files site in BigQuery:
- Big Query Dataset - Name of the BigQuery dataset the raw files reside.
- Big Query Table - Name of the raw data table.
- Incremental Load - Toggle on to persist previously processed data.
- Sharded - Toggle on so Syntasa understands how to query the raw data table.
- Configure "Schema" screen - The schema screen provides the means to give Syntasa instructions how to handle the data partitioning and what the columns mean. This screen is pre-populated but does provide the ability to modify with export and import options to make modifications in Excel and upload:
- Time Source - date
- Time Format Type - Timestamp
- Time Format - yyyyMMdd
- Add Columns - Click the "Add Columns" button to add any extra columns.
- Clear - Use to remove all schema columns.
- Export - Use to download a version of the schema.
- Import - Use to upload a version of the schema.
- Configure "Output" screen - Provides the ability to specify table name and display name along with file format and compression. Name the table with a short prefix that easily helps to identify the dataset, e.g. ga_global.
- Click the green and white checkmark () to accept changes.
Get Hits
The Get Hits process explodes the hits array creating one row for each hit:
- Click the "Get Hits" process.
- Configure the "Join" screen (only necessary if using more than one table as input to the process).
- Configure the "Mapping" screen:
- Toggle "Is Partitioned" off.
- Click "Add All" to add all columns from the input table created from the previous process.
- Change the "hits" Function so that the hits variable is inside an explode() function, i.e. explode(session.hits).
- Toggle "Is Partitioned" to on and a "getPartition()" column should be created at the bottom of the mappings list.
- Configure the "Filters" screen (if necessary).
- Configure Output providing a table name using a similar naming convention as the previous process.
- Click the green and white checkmark () to accept changes.
Explode Products
The Explode Products process takes the product array and creates one row per product:
- Click the "Explode Products" process.
- Configure 'Join' screen (only necessary if using more than one table as input to the process):
- Toggle "Is Partitioned" off.
- Click "Add All" to add all columns from the input table created from the previous process.
- Change the "hits" label to "product".
- Change the "product" Function so that the hits.product variable is inside an explode() function, i.e. explode(session.hits.product).
- Toggle "Is Partitioned" to on and a "getPartition()" column should be created at the bottom of the mappings list Configure "Mapping" screen.
- Configure the "Filters" screen (if necessary).
- Configure Output providing a table name using a similar naming convention as the previous process.
- Click the green and white checkmark () to accept changes.
- Click the "Save & Lock" button (
) to ensure configurations are committed and workflow locked.
Syntasa Development & Production Concept
The Syntasa application has the concept of development workflow and production workflow. Changes are only made in the development workflow. Once changes are tested and validated, the workflow can then be deployed to production. From there, a production job can be configured, executed and scheduled (scheduling includes triggering (App/Process/Time).
Test in Development
Now you're ready to test the configuration:
- Click on the "From BQ" and "Get Hits" nodes while holding the shift key. The nodes will be highlighted in grey with a tick (see the below screenshot) to indicate it's been selected.
- Click on the"Job" button (
) on the top-right. - Click "Create New +" (
) from the drop-down. - A "Create Job" window will appear:
- Name - Descriptive name of the job to facilitate searching for finding job logs for troubleshooting.
- Description (optional) - Detailed description of the purpose of the job.
- Define a Tag (optional) - Custom free text field to help segment jobs in the job logs.
- Process name - Auto-populated, not editable.
- Runtime Type - Auto-populated, not editable.
- Runtime - Dropdown to choose the type/size of the cluster necessary for processing.
- Process Mode - In Development workflow use "Replace Date Range" for most cases and "Drop and Replace" when table structure is modified.
- Date Range - In Development workflow use "Custom" and select the "From Date" and "To Date"
- Advanced: use the default settings.
- Click "Save & Execute" (
) to start the job. - Click the "Activity" icon (
) to expand and show job details on the right side menu. - When the job completes the process nodes will either be solid green to signify success or red to signify failure.
- To verify the job successfully delivered the file check in the S3 bucket for the file.
Run Job in Production
- Initial deploy. From the development workflow, click the "Deploy" button (
). - A deploy screen should appear. Click the "Deploy" button (
). - Go to the Production Workflow.
- Hold shift and click on the "AA Loader" and "Event Enrich" processes.
- Click on the"Job" button () on the top-right.
- Click "Create New +" () from the drop-down.
- Click the "Activity" icon () to expand and show job details on the right side menu.
- To verify the job successfully delivered the file check in the GCS bucket for the file.
Data Processing Validation
After successfully running the processes the data will get loaded into storage and made available for the user base to start analyzing. The best method to ensure the job ran is to check the process output node overview using the following steps:
- Click the "Get Hits" output node labeled with "GA Session Data".
- "Details" provides information about where the data is stored, format and partitioning.
- "Schema" provides the name of the data set columns.
- "State" and verify the dates exist and there is a record count.
- "Preview" provides a 100-row sample of the data to provide the ability to run quick data point checks without running a query.
- Click the gray X (
) at the top-right of the window to close.
Output
Depending on the environment and data writing settings there could be multiple places the data resides. For environments such as GCP the data will be written to GCS and most likely written to BigQuery using the table naming structure specified in each of the process nodes to provide an easy and fast method for users to analyze the data. The data residing in GCS is accessible via Hive command line and only accessible with an actively running cluster. GCS data is typically made available for other Syntasa process.
Additional Configurations
Some additional transformations that can be added to the default pipeline are to explode the sessions and hits arrays. By exploding these a column is being added for the individual dimensions and metrics that are within the array.
Flatten Sessions
To flatten the sessions a Spark processor node must be attached to the output of the "From BQ" process and below code placed into the processor. Please follow the below instructions:
- Go to the Development workflow of the GA input adapter app.
- Unlock the workflow to allow for modifications.
- Drag and drop the "Spark Processor" process on to the workflow canvas.
- Connect the "Spark Processor" to the "From BQ" output node.
- Click on the "Spark Processor" to open.
- Select "Scala" as the "Language".
- Paste the below code into the process text editor.
- Click the green and white checkmark (
) to accept changes. - Click the "Save & Lock" button () to ensure configurations are committed and workflow locked.
- Run a test job.
Flatten Sessions Code
// import org.apache.spark.sql.functions.array_position
val tb = spark.sql("""
SELECT
visitorId visitorId,
visitNumber visitNumber,
visitId visitId,
visitStartTime visitStartTime,
date date,
totals.visits totals_visits,
totals.hits totals_hits,
totals.pageviews totals_pageviews,
totals.timeOnSite totals_timeOnSite,
totals.bounces totals_bounces,
totals.transactions totals_transactions,
totals.transactionRevenue totals_transactionRevenue,
totals.newVisits totals_newVisits,
totals.screenviews totals_screenviews,
totals.uniqueScreenviews totals_uniqueScreenviews,
totals.timeOnScreen totals_timeOnScreen,
totals.totalTransactionRevenue totals_totalTransactionRevenue,
totals.sessionQualityDim totals_sessionQualityDim,
trafficSource.referralPath trafficSource_referralPath,
trafficSource.campaign trafficSource_campaign,
trafficSource.source trafficSource_source,
trafficSource.medium trafficSource_medium,
trafficSource.keyword trafficSource_keyword,
trafficSource.adContent trafficSource_adContent,
trafficSource.adwordsClickInfo.campaignId trafficSource_adwordsClickInfo_campaignId,
trafficSource.adwordsClickInfo.adGroupId trafficSource_adwordsClickInfo_adGroupId,
trafficSource.adwordsClickInfo.creativeId trafficSource_adwordsClickInfo_creativeId,
trafficSource.adwordsClickInfo.criteriaId trafficSource_adwordsClickInfo_criteriaId,
trafficSource.adwordsClickInfo.page trafficSource_adwordsClickInfo_page,
trafficSource.adwordsClickInfo.slot trafficSource_adwordsClickInfo_slot,
trafficSource.adwordsClickInfo.criteriaParameters trafficSource_adwordsClickInfo_criteriaParameters,
trafficSource.adwordsClickInfo.gclId trafficSource_adwordsClickInfo_gclId,
trafficSource.adwordsClickInfo.customerId trafficSource_adwordsClickInfo_customerId,
trafficSource.adwordsClickInfo.adNetworkType trafficSource_adwordsClickInfo_adNetworkType,
trafficSource.adwordsClickInfo.targetingCriteria.boomUserlistId trafficSource_adwordsClickInfo_targetingCriteria_boomUserlistId,
trafficSource.adwordsClickInfo.isVideoAd trafficSource_adwordsClickInfo_isVideoAd,
trafficSource.isTrueDirect trafficSource_isTrueDirect,
trafficSource.campaignCode trafficSource_campaignCode,
device.browser device_browser,
device.browserVersion device_browserVersion,
device.browserSize device_browserSize,
device.operatingSystem device_operatingSystem,
device.operatingSystemVersion device_operatingSystemVersion,
device.isMobile device_isMobile,
device.mobileDeviceBranding device_mobileDeviceBranding,
device.mobileDeviceModel device_mobileDeviceModel,
device.mobileInputSelector device_mobileInputSelector,
device.mobileDeviceInfo device_mobileDeviceInfo,
device.mobileDeviceMarketingName device_mobileDeviceMarketingName,
device.flashVersion device_flashVersion,
device.javaEnabled device_javaEnabled,
device.language device_language,
device.screenColors device_screenColors,
device.screenResolution device_screenResolution,
device.deviceCategory device_deviceCategory,
geoNetwork.continent geoNetwork_continent,
geoNetwork.subContinent geoNetwork_subContinent,
geoNetwork.country geoNetwork_country,
geoNetwork.region geoNetwork_region,
geoNetwork.metro geoNetwork_metro,
geoNetwork.city geoNetwork_city,
geoNetwork.cityId geoNetwork_cityId,
geoNetwork.networkDomain geoNetwork_networkDomain,
geoNetwork.latitude geoNetwork_latitude,
geoNetwork.longitude geoNetwork_longitude,
geoNetwork.networkLocation geoNetwork_networkLocation,
fullVisitorId fullVisitorId,
userId userId,
clientId clientId,
channelGrouping channelGrouping,
socialEngagementType,
if(array_contains(customDimensions.index, 0), customDimensions.value[array_position(customDimensions.index, 0) - 1], null) cd0,
if(array_contains(customDimensions.index, 1), customDimensions.value[array_position(customDimensions.index, 1) - 1], null) cd1,
if(array_contains(customDimensions.index, 2), customDimensions.value[array_position(customDimensions.index, 2) - 1], null) cd2,
if(array_contains(customDimensions.index, 3), customDimensions.value[array_position(customDimensions.index, 3) - 1], null) cd3,
if(array_contains(customDimensions.index, 4), customDimensions.value[array_position(customDimensions.index, 4) - 1], null) cd4,
if(array_contains(customDimensions.index, 5), customDimensions.value[array_position(customDimensions.index, 5) - 1], null) cd5,
if(array_contains(customDimensions.index, 6), customDimensions.value[array_position(customDimensions.index, 6) - 1], null) cd6,
if(array_contains(customDimensions.index, 7), customDimensions.value[array_position(customDimensions.index, 7) - 1], null) cd7,
if(array_contains(customDimensions.index, 8), customDimensions.value[array_position(customDimensions.index, 8) - 1], null) cd8,
if(array_contains(customDimensions.index, 9), customDimensions.value[array_position(customDimensions.index, 9) - 1], null) cd9,
if(array_contains(customDimensions.index, 10), customDimensions.value[array_position(customDimensions.index, 10) - 1], null) cd10,
if(array_contains(customDimensions.index, 11), customDimensions.value[array_position(customDimensions.index, 11) - 1], null) cd11,
if(array_contains(customDimensions.index, 12), customDimensions.value[array_position(customDimensions.index, 12) - 1], null) cd12,
if(array_contains(customDimensions.index, 13), customDimensions.value[array_position(customDimensions.index, 13) - 1], null) cd13,
if(array_contains(customDimensions.index, 14), customDimensions.value[array_position(customDimensions.index, 14) - 1], null) cd14,
if(array_contains(customDimensions.index, 15), customDimensions.value[array_position(customDimensions.index, 15) - 1], null) cd15,
if(array_contains(customDimensions.index, 16), customDimensions.value[array_position(customDimensions.index, 16) - 1], null) cd16,
if(array_contains(customDimensions.index, 17), customDimensions.value[array_position(customDimensions.index, 17) - 1], null) cd17,
if(array_contains(customDimensions.index, 18), customDimensions.value[array_position(customDimensions.index, 18) - 1], null) cd18,
if(array_contains(customDimensions.index, 19), customDimensions.value[array_position(customDimensions.index, 19) - 1], null) cd19,
if(array_contains(customDimensions.index, 20), customDimensions.value[array_position(customDimensions.index, 20) - 1], null) cd20,
if(array_contains(customDimensions.index, 21), customDimensions.value[array_position(customDimensions.index, 21) - 1], null) cd21,
if(array_contains(customDimensions.index, 22), customDimensions.value[array_position(customDimensions.index, 22) - 1], null) cd22,
if(array_contains(customDimensions.index, 23), customDimensions.value[array_position(customDimensions.index, 23) - 1], null) cd23,
if(array_contains(customDimensions.index, 24), customDimensions.value[array_position(customDimensions.index, 24) - 1], null) cd24,
if(array_contains(customDimensions.index, 25), customDimensions.value[array_position(customDimensions.index, 25) - 1], null) cd25,
if(array_contains(customDimensions.index, 26), customDimensions.value[array_position(customDimensions.index, 26) - 1], null) cd26,
if(array_contains(customDimensions.index, 27), customDimensions.value[array_position(customDimensions.index, 27) - 1], null) cd27,
if(array_contains(customDimensions.index, 28), customDimensions.value[array_position(customDimensions.index, 28) - 1], null) cd28,
if(array_contains(customDimensions.index, 29), customDimensions.value[array_position(customDimensions.index, 29) - 1], null) cd29,
if(array_contains(customDimensions.index, 30), customDimensions.value[array_position(customDimensions.index, 30) - 1], null) cd30,
if(array_contains(customDimensions.index, 31), customDimensions.value[array_position(customDimensions.index, 31) - 1], null) cd31,
if(array_contains(customDimensions.index, 32), customDimensions.value[array_position(customDimensions.index, 32) - 1], null) cd32,
if(array_contains(customDimensions.index, 33), customDimensions.value[array_position(customDimensions.index, 33) - 1], null) cd33,
if(array_contains(customDimensions.index, 34), customDimensions.value[array_position(customDimensions.index, 34) - 1], null) cd34,
if(array_contains(customDimensions.index, 35), customDimensions.value[array_position(customDimensions.index, 35) - 1], null) cd35,
if(array_contains(customDimensions.index, 36), customDimensions.value[array_position(customDimensions.index, 36) - 1], null) cd36,
if(array_contains(customDimensions.index, 37), customDimensions.value[array_position(customDimensions.index, 37) - 1], null) cd37,
if(array_contains(customDimensions.index, 38), customDimensions.value[array_position(customDimensions.index, 38) - 1], null) cd38,
if(array_contains(customDimensions.index, 39), customDimensions.value[array_position(customDimensions.index, 39) - 1], null) cd39,
if(array_contains(customDimensions.index, 40), customDimensions.value[array_position(customDimensions.index, 40) - 1], null) cd40,
if(array_contains(customDimensions.index, 41), customDimensions.value[array_position(customDimensions.index, 41) - 1], null) cd41,
if(array_contains(customDimensions.index, 42), customDimensions.value[array_position(customDimensions.index, 42) - 1], null) cd42,
if(array_contains(customDimensions.index, 43), customDimensions.value[array_position(customDimensions.index, 43) - 1], null) cd43,
if(array_contains(customDimensions.index, 44), customDimensions.value[array_position(customDimensions.index, 44) - 1], null) cd44,
if(array_contains(customDimensions.index, 45), customDimensions.value[array_position(customDimensions.index, 45) - 1], null) cd45,
if(array_contains(customDimensions.index, 46), customDimensions.value[array_position(customDimensions.index, 46) - 1], null) cd46,
if(array_contains(customDimensions.index, 47), customDimensions.value[array_position(customDimensions.index, 47) - 1], null) cd47,
if(array_contains(customDimensions.index, 48), customDimensions.value[array_position(customDimensions.index, 48) - 1], null) cd48,
if(array_contains(customDimensions.index, 49), customDimensions.value[array_position(customDimensions.index, 49) - 1], null) cd49,
if(array_contains(customDimensions.index, 50), customDimensions.value[array_position(customDimensions.index, 50) - 1], null) cd50,
if(array_contains(customDimensions.index, 51), customDimensions.value[array_position(customDimensions.index, 51) - 1], null) cd51,
if(array_contains(customDimensions.index, 52), customDimensions.value[array_position(customDimensions.index, 52) - 1], null) cd52,
if(array_contains(customDimensions.index, 53), customDimensions.value[array_position(customDimensions.index, 53) - 1], null) cd53,
if(array_contains(customDimensions.index, 54), customDimensions.value[array_position(customDimensions.index, 54) - 1], null) cd54,
if(array_contains(customDimensions.index, 55), customDimensions.value[array_position(customDimensions.index, 55) - 1], null) cd55,
if(array_contains(customDimensions.index, 56), customDimensions.value[array_position(customDimensions.index, 56) - 1], null) cd56,
if(array_contains(customDimensions.index, 57), customDimensions.value[array_position(customDimensions.index, 57) - 1], null) cd57,
if(array_contains(customDimensions.index, 58), customDimensions.value[array_position(customDimensions.index, 58) - 1], null) cd58,
if(array_contains(customDimensions.index, 59), customDimensions.value[array_position(customDimensions.index, 59) - 1], null) cd59,
if(array_contains(customDimensions.index, 60), customDimensions.value[array_position(customDimensions.index, 60) - 1], null) cd60,
if(array_contains(customDimensions.index, 61), customDimensions.value[array_position(customDimensions.index, 61) - 1], null) cd61,
if(array_contains(customDimensions.index, 62), customDimensions.value[array_position(customDimensions.index, 62) - 1], null) cd62,
if(array_contains(customDimensions.index, 63), customDimensions.value[array_position(customDimensions.index, 63) - 1], null) cd63,
if(array_contains(customDimensions.index, 64), customDimensions.value[array_position(customDimensions.index, 64) - 1], null) cd64,
if(array_contains(customDimensions.index, 65), customDimensions.value[array_position(customDimensions.index, 65) - 1], null) cd65,
if(array_contains(customDimensions.index, 66), customDimensions.value[array_position(customDimensions.index, 66) - 1], null) cd66,
if(array_contains(customDimensions.index, 67), customDimensions.value[array_position(customDimensions.index, 67) - 1], null) cd67,
if(array_contains(customDimensions.index, 68), customDimensions.value[array_position(customDimensions.index, 68) - 1], null) cd68,
if(array_contains(customDimensions.index, 69), customDimensions.value[array_position(customDimensions.index, 69) - 1], null) cd69,
if(array_contains(customDimensions.index, 70), customDimensions.value[array_position(customDimensions.index, 70) - 1], null) cd70,
if(array_contains(customDimensions.index, 71), customDimensions.value[array_position(customDimensions.index, 71) - 1], null) cd71,
if(array_contains(customDimensions.index, 72), customDimensions.value[array_position(customDimensions.index, 72) - 1], null) cd72,
if(array_contains(customDimensions.index, 73), customDimensions.value[array_position(customDimensions.index, 73) - 1], null) cd73,
if(array_contains(customDimensions.index, 74), customDimensions.value[array_position(customDimensions.index, 74) - 1], null) cd74,
if(array_contains(customDimensions.index, 75), customDimensions.value[array_position(customDimensions.index, 75) - 1], null) cd75,
if(array_contains(customDimensions.index, 76), customDimensions.value[array_position(customDimensions.index, 76) - 1], null) cd76,
if(array_contains(customDimensions.index, 77), customDimensions.value[array_position(customDimensions.index, 77) - 1], null) cd77,
if(array_contains(customDimensions.index, 78), customDimensions.value[array_position(customDimensions.index, 78) - 1], null) cd78,
if(array_contains(customDimensions.index, 79), customDimensions.value[array_position(customDimensions.index, 79) - 1], null) cd79,
if(array_contains(customDimensions.index, 80), customDimensions.value[array_position(customDimensions.index, 80) - 1], null) cd80,
file_date event_partition
FROM
@InputTable1
WHERE
file_date between @fromDate and @toDate
""")
tb.createOrReplaceTempView("tb")
spark.sql("""
CREATE TABLE IF NOT EXISTS @OutputTable1
(visitorId long,
visitNumber long,
visitId long,
visitStartTime long,
date string,
totals_visits long,
totals_hits long,
totals_pageviews long,
totals_timeOnSite long,
totals_bounces long,
totals_transactions long,
totals_transactionRevenue long,
totals_newVisits long,
totals_screenviews long,
totals_uniqueScreenviews long,
totals_timeOnScreen long,
totals_totalTransactionRevenue long,
totals_sessionQualityDim long,
trafficSource_referralPath string,
trafficSource_campaign string,
trafficSource_source string,
trafficSource_medium string,
trafficSource_keyword string,
trafficSource_adContent string,
trafficSource_adwordsClickInfo_campaignId long,
trafficSource_adwordsClickInfo_adGroupId long,
trafficSource_adwordsClickInfo_creativeId long,
trafficSource_adwordsClickInfo_criteriaId long,
trafficSource_adwordsClickInfo_page long,
trafficSource_adwordsClickInfo_slot string,
trafficSource_adwordsClickInfo_criteriaParameters string,
trafficSource_adwordsClickInfo_gclId string,
trafficSource_adwordsClickInfo_customerId long,
trafficSource_adwordsClickInfo_adNetworkType string,
trafficSource_adwordsClickInfo_targetingCriteria_boomUserlistId long,
trafficSource_adwordsClickInfo_isVideoAd boolean,
trafficSource_isTrueDirect boolean,
trafficSource_campaignCode string,
device_browser string,
device_browserVersion string,
device_browserSize string,
device_operatingSystem string,
device_operatingSystemVersion string,
device_isMobile boolean,
device_mobileDeviceBranding string,
device_mobileDeviceModel string,
device_mobileInputSelector string,
device_mobileDeviceInfo string,
device_mobileDeviceMarketingName string,
device_flashVersion string,
device_javaEnabled boolean,
device_language string,
device_screenColors string,
device_screenResolution string,
device_deviceCategory string,
geoNetwork_continent string,
geoNetwork_subContinent string,
geoNetwork_country string,
geoNetwork_region string,
geoNetwork_metro string,
geoNetwork_city string,
geoNetwork_cityId string,
geoNetwork_networkDomain string,
geoNetwork_latitude string,
geoNetwork_longitude string,
geoNetwork_networkLocation string,
fullVisitorId string,
userId string,
clientId string,
channelGrouping string,
socialEngagementType string,
customDimension0 string,
customDimension1 string,
customDimension2 string,
customDimension3 string,
customDimension4 string,
customDimension5 string,
customDimension6 string,
customDimension7 string,
customDimension8 string,
customDimension9 string,
customDimension10 string,
customDimension11 string,
customDimension12 string,
customDimension13 string,
customDimension14 string,
customDimension15 string,
customDimension16 string,
customDimension17 string,
customDimension18 string,
customDimension19 string,
customDimension20 string,
customDimension21 string,
customDimension22 string,
customDimension23 string,
customDimension24 string,
customDimension25 string,
customDimension26 string,
customDimension27 string,
customDimension28 string,
customDimension29 string,
customDimension30 string,
customDimension31 string,
customDimension32 string,
customDimension33 string,
customDimension34 string,
customDimension35 string,
customDimension36 string,
customDimension37 string,
customDimension38 string,
customDimension39 string,
customDimension40 string,
customDimension41 string,
customDimension42 string,
customDimension43 string,
customDimension44 string,
customDimension45 string,
customDimension46 string,
customDimension47 string,
customDimension48 string,
customDimension49 string,
customDimension50 string,
customDimension51 string,
customDimension52 string,
customDimension53 string,
customDimension54 string,
customDimension55 string,
customDimension56 string,
customDimension57 string,
customDimension58 string,
customDimension59 string,
customDimension60 string,
customDimension61 string,
customDimension62 string,
customDimension63 string,
customDimension64 string,
customDimension65 string,
customDimension66 string,
customDimension67 string,
customDimension68 string,
customDimension69 string,
customDimension70 string,
customDimension71 string,
customDimension72 string,
customDimension73 string,
customDimension74 string,
customDimension75 string,
customDimension76 string,
customDimension77 string,
customDimension78 string,
customDimension79 string,
customDimension80 string)
PARTITIONED BY (event_partition string)
LOCATION @location
STORED AS parquet
""")
spark.sql("set hive.exec.dynamic.partition.mode=nonstrict")
spark.sql("""
INSERT OVERWRITE TABLE @OutputTable1
SELECT
*
FROM
tb
""")
Flatten Hits
To flatten the hits a Spark processor node must be attached to the output of the "Get Hits" process and below code placed into the processor. Please follow the below instructions:
- Go to the Development workflow of the GA input adapter app.
- Unlock the workflow to allow for modifications.
- Drag and drop the "Spark Processor" process on to the workflow canvas.
- Connect the "Spark Processor" to the "From BQ" output node.
- Click on the "Spark Processor" to open.
- Select "Scala" as the "Language".
- Paste the below code into the process text editor.
- Click the green and white checkmark () to accept changes.
- Click the "Save & Lock" button () to ensure configurations are committed and workflow locked.
- Run a test job.
Flatten Hits Code
val tb = spark.sql("""
SELECT
visitorId visitorId,
visitNumber visitNumber,
visitId visitId,
visitStartTime visitStartTime,
date date,
totals.bounces totals_bounces,
totals.hits totals_hits,
totals.newVisits totals_newVisits,
totals.pageviews totals_pageviews,
totals.screenviews totals_screenviews,
totals.sessionQualityDim totals_sessionQualityDim,
totals.timeOnScreen totals_timeOnScreen,
totals.timeOnSite totals_timeOnSite,
totals.totalTransactionRevenue totals_totalTransactionRevenue,
totals.transactionRevenue totals_transactionRevenue,
totals.transactions totals_transactions,
totals.UniqueScreenViews totals_UniqueScreenViews,
totals.visits totals_visits,
concat_ws(",", transform(hits.product, x -> x.productbrand)) productbrand,
concat_ws(",", transform(hits.product, x -> x.v2productcategory)) v2productcategory,
concat_ws(",", transform(hits.product, x -> x.v2productname)) v2productname,
case when hits.eCommerceAction.action_type = 1 then 1 else 0 end click_product_lists,
case when hits.eCommerceAction.action_type = 2 then 1 else 0 end product_detail_views,
case when hits.eCommerceAction.action_type = 3 then 1 else 0 end cart_add,
case when hits.eCommerceAction.action_type = 4 then 1 else 0 end cart_remove,
case when hits.eCommerceAction.action_type = 5 then 1 else 0 end checkout,
case when hits.eCommerceAction.action_type = 6 then 1 else 0 end completed_purchase,
case when hits.eCommerceAction.action_type = 7 then 1 else 0 end refund,
case when hits.eCommerceAction.action_type = 8 then 1 else 0 end checkout_options,
trafficSource.referralPath trafficSource_referralPath,
trafficSource.campaign trafficSource_campaign,
trafficSource.source trafficSource_source,
trafficSource.medium trafficSource_medium,
trafficSource.keyword trafficSource_keyword,
trafficSource.adContent trafficSource_adContent,
trafficSource.adwordsClickInfo.campaignId trafficSource_adwordsClickInfo_campaignId,
trafficSource.adwordsClickInfo.adGroupId trafficSource_adwordsClickInfo_adGroupId,
trafficSource.adwordsClickInfo.creativeId trafficSource_adwordsClickInfo_creativeId,
trafficSource.adwordsClickInfo.criteriaId trafficSource_adwordsClickInfo_criteriaId,
trafficSource.adwordsClickInfo.page trafficSource_adwordsClickInfo_page,
trafficSource.adwordsClickInfo.slot trafficSource_adwordsClickInfo_slot,
trafficSource.adwordsClickInfo.criteriaParameters trafficSource_adwordsClickInfo_criteriaParameters,
trafficSource.adwordsClickInfo.gclId trafficSource_adwordsClickInfo_gclId,
trafficSource.adwordsClickInfo.customerId trafficSource_adwordsClickInfo_customerId,
trafficSource.adwordsClickInfo.adNetworkType trafficSource_adwordsClickInfo_adNetworkType,
trafficSource.adwordsClickInfo.targetingCriteria.boomUserlistId trafficSource_adwordsClickInfo_targetingCriteria_boomUserlistId,
trafficSource.adwordsClickInfo.isVideoAd trafficSource_adwordsClickInfo_isVideoAd,
trafficSource.isTrueDirect trafficSource_isTrueDirect,
trafficSource.campaignCode trafficSource_campaignCode,
device.browser device_browser,
device.browserVersion device_browserVersion,
device.browserSize device_browserSize,
device.operatingSystem device_operatingSystem,
device.operatingSystemVersion device_operatingSystemVersion,
device.isMobile device_isMobile,
device.mobileDeviceBranding device_mobileDeviceBranding,
device.mobileDeviceModel device_mobileDeviceModel,
device.mobileInputSelector device_mobileInputSelector,
device.mobileDeviceInfo device_mobileDeviceInfo,
device.mobileDeviceMarketingName device_mobileDeviceMarketingName,
device.flashVersion device_flashVersion,
device.javaEnabled device_javaEnabled,
device.language device_language,
device.screenColors device_screenColors,
device.screenResolution device_screenResolution,
device.deviceCategory device_deviceCategory,
geoNetwork.continent geoNetwork_continent,
geoNetwork.subContinent geoNetwork_subContinent,
geoNetwork.country geoNetwork_country,
geoNetwork.region geoNetwork_region,
geoNetwork.metro geoNetwork_metro,
geoNetwork.city geoNetwork_city,
geoNetwork.cityId geoNetwork_cityId,
geoNetwork.networkDomain geoNetwork_networkDomain,
geoNetwork.latitude geoNetwork_latitude,
geoNetwork.longitude geoNetwork_longitude,
geoNetwork.networkLocation geoNetwork_networkLocation,
hits.hitNumber hits_hitNumber,
hits.time hits_time,
hits.hour hits_hour,
hits.minute hits_minute,
hits.isSecure hits_isSecure,
hits.isInteraction hits_isInteraction,
hits.isEntrance hits_isEntrance,
hits.isExit hits_isExit,
hits.referer hits_referer,
hits.page.pagePath hits_page_pagePath,
hits.page.hostname hits_page_hostname,
hits.page.pageTitle hits_page_pageTitle,
hits.page.searchKeyword hits_page_searchKeyword,
hits.page.searchCategory hits_page_searchCategory,
hits.page.pagePathLevel1 hits_page_pagePathLevel1,
hits.page.pagePathLevel2 hits_page_pagePathLevel2,
hits.page.pagePathLevel3 hits_page_pagePathLevel3,
hits.page.pagePathLevel4 hits_page_pagePathLevel4,
hits.transaction.transactionId hits_transaction_transactionId,
hits.transaction.transactionRevenue hits_transaction_transactionRevenue,
hits.transaction.transactionTax hits_transaction_transactionTax,
hits.transaction.transactionShipping hits_transaction_transactionShipping,
hits.transaction.affiliation hits_transaction_affiliation,
hits.transaction.currencyCode hits_transaction_currencyCode,
hits.transaction.localTransactionRevenue hits_transaction_localTransactionRevenue,
hits.transaction.localTransactionTax hits_transaction_localTransactionTax,
hits.transaction.localTransactionShipping hits_transaction_localTransactionShipping,
hits.transaction.transactionCoupon hits_transaction_transactionCoupon,
hits.item.transactionId hits_item_transactionId,
hits.item.productName hits_item_productName,
hits.item.productCategory hits_item_productCategory,
hits.item.productSku hits_item_productSku,
hits.item.itemQuantity hits_item_itemQuantity,
hits.item.itemRevenue hits_item_itemRevenue,
hits.item.currencyCode hits_item_currencyCode,
hits.item.localItemRevenue hits_item_localItemRevenue,
hits.contentInfo.contentDescription hits_contentInfo_contentDescription,
hits.appInfo.name hits_appInfo_name,
hits.appInfo.version hits_appInfo_version,
hits.appInfo.id hits_appInfo_id,
hits.appInfo.installerId hits_appInfo_installerId,
hits.appInfo.appInstallerId hits_appInfo_appInstallerId,
hits.appInfo.appName hits_appInfo_appName,
hits.appInfo.appVersion hits_appInfo_appVersion,
hits.appInfo.appId hits_appInfo_appId,
hits.appInfo.screenName hits_appInfo_screenName,
hits.appInfo.landingScreenName hits_appInfo_landingScreenName,
hits.appInfo.exitScreenName hits_appInfo_exitScreenName,
hits.appInfo.screenDepth hits_appInfo_screenDepth,
hits.exceptionInfo.description hits_exceptionInfo_description,
hits.exceptionInfo.isFatal hits_exceptionInfo_isFatal,
hits.exceptionInfo.exceptions hits_exceptionInfo_exceptions,
hits.exceptionInfo.fatalExceptions hits_exceptionInfo_fatalExceptions,
hits.eventInfo.eventCategory hits_eventInfo_eventCategory,
hits.eventInfo.eventAction hits_eventInfo_eventAction,
hits.eventInfo.eventLabel hits_eventInfo_eventLabel,
hits.eventInfo.eventValue hits_eventInfo_eventValue,
hits.promotionActionInfo.promoIsView hits_promotionActionInfo_promoIsView,
hits.promotionActionInfo.promoIsClick hits_promotionActionInfo_promoIsClick,
hits.refund.refundAmount hits_refund_refundAmount,
hits.refund.localRefundAmount hits_refund_localRefundAmount,
hits.eCommerceAction.action_type hits_eCommerceAction_action_type,
hits.eCommerceAction.step hits_eCommerceAction_step,
hits.eCommerceAction.option hits_eCommerceAction_option,
hits.publisher.dfpClicks hits_publisher_dfpClicks,
hits.publisher.dfpImpressions hits_publisher_dfpImpressions,
hits.publisher.dfpMatchedQueries hits_publisher_dfpMatchedQueries,
hits.publisher.dfpMeasurableImpressions hits_publisher_dfpMeasurableImpressions,
hits.publisher.dfpQueries hits_publisher_dfpQueries,
hits.publisher.dfpRevenueCpm hits_publisher_dfpRevenueCpm,
hits.publisher.dfpRevenueCpc hits_publisher_dfpRevenueCpc,
hits.publisher.dfpViewableImpressions hits_publisher_dfpViewableImpressions,
hits.publisher.dfpPagesViewed hits_publisher_dfpPagesViewed,
hits.publisher.adsenseBackfillDfpClicks hits_publisher_adsenseBackfillDfpClicks,
hits.publisher.adsenseBackfillDfpImpressions hits_publisher_adsenseBackfillDfpImpressions,
hits.publisher.adsenseBackfillDfpMatchedQueries hits_publisher_adsenseBackfillDfpMatchedQueries,
hits.publisher.adsenseBackfillDfpMeasurableImpressions hits_publisher_adsenseBackfillDfpMeasurableImpressions,
hits.publisher.adsenseBackfillDfpQueries hits_publisher_adsenseBackfillDfpQueries,
hits.publisher.adsenseBackfillDfpRevenueCpm hits_publisher_adsenseBackfillDfpRevenueCpm,
hits.publisher.adsenseBackfillDfpRevenueCpc hits_publisher_adsenseBackfillDfpRevenueCpc,
hits.publisher.adsenseBackfillDfpViewableImpressions hits_publisher_adsenseBackfillDfpViewableImpressions,
hits.publisher.adsenseBackfillDfpPagesViewed hits_publisher_adsenseBackfillDfpPagesViewed,
hits.publisher.adxBackfillDfpClicks hits_publisher_adxBackfillDfpClicks,
hits.publisher.adxBackfillDfpImpressions hits_publisher_adxBackfillDfpImpressions,
hits.publisher.adxBackfillDfpMatchedQueries hits_publisher_adxBackfillDfpMatchedQueries,
hits.publisher.adxBackfillDfpMeasurableImpressions hits_publisher_adxBackfillDfpMeasurableImpressions,
hits.publisher.adxBackfillDfpQueries hits_publisher_adxBackfillDfpQueries,
hits.publisher.adxBackfillDfpRevenueCpm hits_publisher_adxBackfillDfpRevenueCpm,
hits.publisher.adxBackfillDfpRevenueCpc hits_publisher_adxBackfillDfpRevenueCpc,
hits.publisher.adxBackfillDfpViewableImpressions hits_publisher_adxBackfillDfpViewableImpressions,
hits.publisher.adxBackfillDfpPagesViewed hits_publisher_adxBackfillDfpPagesViewed,
hits.publisher.adxClicks hits_publisher_adxClicks,
hits.publisher.adxImpressions hits_publisher_adxImpressions,
hits.publisher.adxMatchedQueries hits_publisher_adxMatchedQueries,
hits.publisher.adxMeasurableImpressions hits_publisher_adxMeasurableImpressions,
hits.publisher.adxQueries hits_publisher_adxQueries,
hits.publisher.adxRevenue hits_publisher_adxRevenue,
hits.publisher.adxViewableImpressions hits_publisher_adxViewableImpressions,
hits.publisher.adxPagesViewed hits_publisher_adxPagesViewed,
hits.publisher.adsViewed hits_publisher_adsViewed,
hits.publisher.adsUnitsViewed hits_publisher_adsUnitsViewed,
hits.publisher.adsUnitsMatched hits_publisher_adsUnitsMatched,
hits.publisher.viewableAdsViewed hits_publisher_viewableAdsViewed,
hits.publisher.measurableAdsViewed hits_publisher_measurableAdsViewed,
hits.publisher.adsPagesViewed hits_publisher_adsPagesViewed,
hits.publisher.adsClicked hits_publisher_adsClicked,
hits.publisher.adsRevenue hits_publisher_adsRevenue,
hits.publisher.dfpAdGroup hits_publisher_dfpAdGroup,
hits.publisher.dfpAdUnits hits_publisher_dfpAdUnits,
hits.publisher.dfpNetworkId hits_publisher_dfpNetworkId,
hits.type hits_type,
hits.social.socialInteractionNetwork hits_social_socialInteractionNetwork,
hits.social.socialInteractionAction hits_social_socialInteractionAction,
hits.social.socialInteractions hits_social_socialInteractions,
hits.social.socialInteractionTarget hits_social_socialInteractionTarget,
hits.social.socialNetwork hits_social_socialNetwork,
hits.social.uniqueSocialInteractions hits_social_uniqueSocialInteractions,
hits.social.hasSocialSourceReferral hits_social_hasSocialSourceReferral,
hits.social.socialInteractionNetworkAction hits_social_socialInteractionNetworkAction,
hits.latencyTracking.pageLoadSample hits_latencyTracking_pageLoadSample,
hits.latencyTracking.pageLoadTime hits_latencyTracking_pageLoadTime,
hits.latencyTracking.pageDownloadTime hits_latencyTracking_pageDownloadTime,
hits.latencyTracking.redirectionTime hits_latencyTracking_redirectionTime,
hits.latencyTracking.speedMetricsSample hits_latencyTracking_speedMetricsSample,
hits.latencyTracking.domainLookupTime hits_latencyTracking_domainLookupTime,
hits.latencyTracking.serverConnectionTime hits_latencyTracking_serverConnectionTime,
hits.latencyTracking.serverResponseTime hits_latencyTracking_serverResponseTime,
hits.latencyTracking.domLatencyMetricsSample hits_latencyTracking_domLatencyMetricsSample,
hits.latencyTracking.domInteractiveTime hits_latencyTracking_domInteractiveTime,
hits.latencyTracking.domContentLoadedTime hits_latencyTracking_domContentLoadedTime,
hits.latencyTracking.userTimingValue hits_latencyTracking_userTimingValue,
hits.latencyTracking.userTimingSample hits_latencyTracking_userTimingSample,
hits.latencyTracking.userTimingVariable hits_latencyTracking_userTimingVariable,
hits.latencyTracking.userTimingCategory hits_latencyTracking_userTimingCategory,
hits.latencyTracking.userTimingLabel hits_latencyTracking_userTimingLabel,
hits.sourcePropertyInfo.sourcePropertyDisplayName hits_sourcePropertyInfo_sourcePropertyDisplayName,
hits.sourcePropertyInfo.sourcePropertyTrackingId hits_sourcePropertyInfo_sourcePropertyTrackingId,
hits.contentGroup.contentGroup1 hits_contentGroup_contentGroup1,
hits.contentGroup.contentGroup2 hits_contentGroup_contentGroup2,
hits.contentGroup.contentGroup3 hits_contentGroup_contentGroup3,
hits.contentGroup.contentGroup4 hits_contentGroup_contentGroup4,
hits.contentGroup.contentGroup5 hits_contentGroup_contentGroup5,
hits.contentGroup.previousContentGroup1 hits_contentGroup_previousContentGroup1,
hits.contentGroup.previousContentGroup2 hits_contentGroup_previousContentGroup2,
hits.contentGroup.previousContentGroup3 hits_contentGroup_previousContentGroup3,
hits.contentGroup.previousContentGroup4 hits_contentGroup_previousContentGroup4,
hits.contentGroup.previousContentGroup5 hits_contentGroup_previousContentGroup5,
hits.contentGroup.contentGroupUniqueViews1 hits_contentGroup_contentGroupUniqueViews1,
hits.contentGroup.contentGroupUniqueViews2 hits_contentGroup_contentGroupUniqueViews2,
hits.contentGroup.contentGroupUniqueViews3 hits_contentGroup_contentGroupUniqueViews3,
hits.contentGroup.contentGroupUniqueViews4 hits_contentGroup_contentGroupUniqueViews4,
hits.contentGroup.contentGroupUniqueViews5 hits_contentGroup_contentGroupUniqueViews5,
hits.dataSource hits_dataSource,
fullVisitorId fullVisitorId,
userId userId,
clientId clientId,
channelGrouping channelGrouping,
socialEngagementType socialEngagementType,
if(size(hits.promotion) > 0, hits.promotion[0].promoId, null) hits_promotion_promoId0,
if(size(hits.promotion) > 0, hits.promotion[0].promoName, null) hits_promotion_promoName0,
if(size(hits.promotion) > 0, hits.promotion[0].promoCreative, null) hits_promotion_promoCreative0,
if(size(hits.promotion) > 0, hits.promotion[0].promoPosition, null) hits_promotion_promoPosition0,
if(size(hits.promotion) > 1, hits.promotion[1].promoId, null) hits_promotion_promoId1,
if(size(hits.promotion) > 1, hits.promotion[1].promoName, null) hits_promotion_promoName1,
if(size(hits.promotion) > 1, hits.promotion[1].promoCreative, null) hits_promotion_promoCreative1,
if(size(hits.promotion) > 1, hits.promotion[1].promoPosition, null) hits_promotion_promoPosition1,
if(size(hits.promotion) > 2, hits.promotion[2].promoId, null) hits_promotion_promoId2,
if(size(hits.promotion) > 2, hits.promotion[2].promoName, null) hits_promotion_promoName2,
if(size(hits.promotion) > 2, hits.promotion[2].promoCreative, null) hits_promotion_promoCreative2,
if(size(hits.promotion) > 2, hits.promotion[2].promoPosition, null) hits_promotion_promoPosition2,
if(size(hits.promotion) > 3, hits.promotion[3].promoId, null) hits_promotion_promoId3,
if(size(hits.promotion) > 3, hits.promotion[3].promoName, null) hits_promotion_promoName3,
if(size(hits.promotion) > 3, hits.promotion[3].promoCreative, null) hits_promotion_promoCreative3,
if(size(hits.promotion) > 3, hits.promotion[3].promoPosition, null) hits_promotion_promoPosition3,
if(size(hits.promotion) > 4, hits.promotion[4].promoId, null) hits_promotion_promoId4,
if(size(hits.promotion) > 4, hits.promotion[4].promoName, null) hits_promotion_promoName4,
if(size(hits.promotion) > 4, hits.promotion[4].promoCreative, null) hits_promotion_promoCreative4,
if(size(hits.promotion) > 4, hits.promotion[4].promoPosition, null) hits_promotion_promoPosition4,
if(size(hits.promotion) > 5, hits.promotion[5].promoId, null) hits_promotion_promoId5,
if(size(hits.promotion) > 5, hits.promotion[5].promoName, null) hits_promotion_promoName5,
if(size(hits.promotion) > 5, hits.promotion[5].promoCreative, null) hits_promotion_promoCreative5,
if(size(hits.promotion) > 5, hits.promotion[5].promoPosition, null) hits_promotion_promoPosition5,
if(size(hits.promotion) > 6, hits.promotion[6].promoId, null) hits_promotion_promoId6,
if(size(hits.promotion) > 6, hits.promotion[6].promoName, null) hits_promotion_promoName6,
if(size(hits.promotion) > 6, hits.promotion[6].promoCreative, null) hits_promotion_promoCreative6,
if(size(hits.promotion) > 6, hits.promotion[6].promoPosition, null) hits_promotion_promoPosition6,
if(size(hits.promotion) > 7, hits.promotion[7].promoId, null) hits_promotion_promoId7,
if(size(hits.promotion) > 7, hits.promotion[7].promoName, null) hits_promotion_promoName7,
if(size(hits.promotion) > 7, hits.promotion[7].promoCreative, null) hits_promotion_promoCreative7,
if(size(hits.promotion) > 7, hits.promotion[7].promoPosition, null) hits_promotion_promoPosition7,
if(size(hits.promotion) > 8, hits.promotion[8].promoId, null) hits_promotion_promoId8,
if(size(hits.promotion) > 8, hits.promotion[8].promoName, null) hits_promotion_promoName8,
if(size(hits.promotion) > 8, hits.promotion[8].promoCreative, null) hits_promotion_promoCreative8,
if(size(hits.promotion) > 8, hits.promotion[8].promoPosition, null) hits_promotion_promoPosition8,
if(size(hits.promotion) > 9, hits.promotion[9].promoId, null) hits_promotion_promoId9,
if(size(hits.promotion) > 9, hits.promotion[9].promoName, null) hits_promotion_promoName9,
if(size(hits.promotion) > 9, hits.promotion[9].promoCreative, null) hits_promotion_promoCreative9,
if(size(hits.promotion) > 9, hits.promotion[9].promoPosition, null) hits_promotion_promoPosition9,
if(size(hits.promotion) > 10, hits.promotion[10].promoId, null) hits_promotion_promoId10,
if(size(hits.promotion) > 10, hits.promotion[10].promoName, null) hits_promotion_promoName10,
if(size(hits.promotion) > 10, hits.promotion[10].promoCreative, null) hits_promotion_promoCreative10,
if(size(hits.promotion) > 10, hits.promotion[10].promoPosition, null) hits_promotion_promoPosition10,
if(size(hits.promotion) > 11, hits.promotion[11].promoId, null) hits_promotion_promoId11,
if(size(hits.promotion) > 11, hits.promotion[11].promoName, null) hits_promotion_promoName11,
if(size(hits.promotion) > 11, hits.promotion[11].promoCreative, null) hits_promotion_promoCreative11,
if(size(hits.promotion) > 11, hits.promotion[11].promoPosition, null) hits_promotion_promoPosition11,
if(size(hits.promotion) > 12, hits.promotion[12].promoId, null) hits_promotion_promoId12,
if(size(hits.promotion) > 12, hits.promotion[12].promoName, null) hits_promotion_promoName12,
if(size(hits.promotion) > 12, hits.promotion[12].promoCreative, null) hits_promotion_promoCreative12,
if(size(hits.promotion) > 12, hits.promotion[12].promoPosition, null) hits_promotion_promoPosition12,
if(size(hits.promotion) > 13, hits.promotion[13].promoId, null) hits_promotion_promoId13,
if(size(hits.promotion) > 13, hits.promotion[13].promoName, null) hits_promotion_promoName13,
if(size(hits.promotion) > 13, hits.promotion[13].promoCreative, null) hits_promotion_promoCreative13,
if(size(hits.promotion) > 13, hits.promotion[13].promoPosition, null) hits_promotion_promoPosition13,
if(size(hits.promotion) > 14, hits.promotion[14].promoId, null) hits_promotion_promoId14,
if(size(hits.promotion) > 14, hits.promotion[14].promoName, null) hits_promotion_promoName14,
if(size(hits.promotion) > 14, hits.promotion[14].promoCreative, null) hits_promotion_promoCreative14,
if(size(hits.promotion) > 14, hits.promotion[14].promoPosition, null) hits_promotion_promoPosition14,
if(size(hits.promotion) > 15, hits.promotion[15].promoId, null) hits_promotion_promoId15,
if(size(hits.promotion) > 15, hits.promotion[15].promoName, null) hits_promotion_promoName15,
if(size(hits.promotion) > 15, hits.promotion[15].promoCreative, null) hits_promotion_promoCreative15,
if(size(hits.promotion) > 15, hits.promotion[15].promoPosition, null) hits_promotion_promoPosition15,
if(size(hits.promotion) > 16, hits.promotion[16].promoId, null) hits_promotion_promoId16,
if(size(hits.promotion) > 16, hits.promotion[16].promoName, null) hits_promotion_promoName16,
if(size(hits.promotion) > 16, hits.promotion[16].promoCreative, null) hits_promotion_promoCreative16,
if(size(hits.promotion) > 16, hits.promotion[16].promoPosition, null) hits_promotion_promoPosition16,
if(size(hits.promotion) > 17, hits.promotion[17].promoId, null) hits_promotion_promoId17,
if(size(hits.promotion) > 17, hits.promotion[17].promoName, null) hits_promotion_promoName17,
if(size(hits.promotion) > 17, hits.promotion[17].promoCreative, null) hits_promotion_promoCreative17,
if(size(hits.promotion) > 17, hits.promotion[17].promoPosition, null) hits_promotion_promoPosition17,
if(size(hits.promotion) > 18, hits.promotion[18].promoId, null) hits_promotion_promoId18,
if(size(hits.promotion) > 18, hits.promotion[18].promoName, null) hits_promotion_promoName18,
if(size(hits.promotion) > 18, hits.promotion[18].promoCreative, null) hits_promotion_promoCreative18,
if(size(hits.promotion) > 18, hits.promotion[18].promoPosition, null) hits_promotion_promoPosition18,
if(size(hits.promotion) > 19, hits.promotion[19].promoId, null) hits_promotion_promoId19,
if(size(hits.promotion) > 19, hits.promotion[19].promoName, null) hits_promotion_promoName19,
if(size(hits.promotion) > 19, hits.promotion[19].promoCreative, null) hits_promotion_promoCreative19,
if(size(hits.promotion) > 19, hits.promotion[19].promoPosition, null) hits_promotion_promoPosition19,
if(size(hits.promotion) > 20, hits.promotion[20].promoId, null) hits_promotion_promoId20,
if(size(hits.promotion) > 20, hits.promotion[20].promoName, null) hits_promotion_promoName20,
if(size(hits.promotion) > 20, hits.promotion[20].promoCreative, null) hits_promotion_promoCreative20,
if(size(hits.promotion) > 20, hits.promotion[20].promoPosition, null) hits_promotion_promoPosition20,
if(size(hits.promotion) > 21, hits.promotion[21].promoId, null) hits_promotion_promoId21,
if(size(hits.promotion) > 21, hits.promotion[21].promoName, null) hits_promotion_promoName21,
if(size(hits.promotion) > 21, hits.promotion[21].promoCreative, null) hits_promotion_promoCreative21,
if(size(hits.promotion) > 21, hits.promotion[21].promoPosition, null) hits_promotion_promoPosition21,
if(size(hits.promotion) > 22, hits.promotion[22].promoId, null) hits_promotion_promoId22,
if(size(hits.promotion) > 22, hits.promotion[22].promoName, null) hits_promotion_promoName22,
if(size(hits.promotion) > 22, hits.promotion[22].promoCreative, null) hits_promotion_promoCreative22,
if(size(hits.promotion) > 22, hits.promotion[22].promoPosition, null) hits_promotion_promoPosition22,
if(size(hits.promotion) > 23, hits.promotion[23].promoId, null) hits_promotion_promoId23,
if(size(hits.promotion) > 23, hits.promotion[23].promoName, null) hits_promotion_promoName23,
if(size(hits.promotion) > 23, hits.promotion[23].promoCreative, null) hits_promotion_promoCreative23,
if(size(hits.promotion) > 23, hits.promotion[23].promoPosition, null) hits_promotion_promoPosition23,
if(size(hits.promotion) > 24, hits.promotion[24].promoId, null) hits_promotion_promoId24,
if(size(hits.promotion) > 24, hits.promotion[24].promoName, null) hits_promotion_promoName24,
if(size(hits.promotion) > 24, hits.promotion[24].promoCreative, null) hits_promotion_promoCreative24,
if(size(hits.promotion) > 24, hits.promotion[24].promoPosition, null) hits_promotion_promoPosition24,
if(size(hits.promotion) > 25, hits.promotion[25].promoId, null) hits_promotion_promoId25,
if(size(hits.promotion) > 25, hits.promotion[25].promoName, null) hits_promotion_promoName25,
if(size(hits.promotion) > 25, hits.promotion[25].promoCreative, null) hits_promotion_promoCreative25,
if(size(hits.promotion) > 25, hits.promotion[25].promoPosition, null) hits_promotion_promoPosition25,
if(size(hits.promotion) > 26, hits.promotion[26].promoId, null) hits_promotion_promoId26,
if(size(hits.promotion) > 26, hits.promotion[26].promoName, null) hits_promotion_promoName26,
if(size(hits.promotion) > 26, hits.promotion[26].promoCreative, null) hits_promotion_promoCreative26,
if(size(hits.promotion) > 26, hits.promotion[26].promoPosition, null) hits_promotion_promoPosition26,
if(size(hits.promotion) > 27, hits.promotion[27].promoId, null) hits_promotion_promoId27,
if(size(hits.promotion) > 27, hits.promotion[27].promoName, null) hits_promotion_promoName27,
if(size(hits.promotion) > 27, hits.promotion[27].promoCreative, null) hits_promotion_promoCreative27,
if(size(hits.promotion) > 27, hits.promotion[27].promoPosition, null) hits_promotion_promoPosition27,
if(size(hits.promotion) > 28, hits.promotion[28].promoId, null) hits_promotion_promoId28,
if(size(hits.promotion) > 28, hits.promotion[28].promoName, null) hits_promotion_promoName28,
if(size(hits.promotion) > 28, hits.promotion[28].promoCreative, null) hits_promotion_promoCreative28,
if(size(hits.promotion) > 28, hits.promotion[28].promoPosition, null) hits_promotion_promoPosition28,
if(size(hits.promotion) > 29, hits.promotion[29].promoId, null) hits_promotion_promoId29,
if(size(hits.promotion) > 29, hits.promotion[29].promoName, null) hits_promotion_promoName29,
if(size(hits.promotion) > 29, hits.promotion[29].promoCreative, null) hits_promotion_promoCreative29,
if(size(hits.promotion) > 29, hits.promotion[29].promoPosition, null) hits_promotion_promoPosition29,
if(size(hits.promotion) > 30, hits.promotion[30].promoId, null) hits_promotion_promoId30,
if(size(hits.promotion) > 30, hits.promotion[30].promoName, null) hits_promotion_promoName30,
if(size(hits.promotion) > 30, hits.promotion[30].promoCreative, null) hits_promotion_promoCreative30,
if(size(hits.promotion) > 30, hits.promotion[30].promoPosition, null) hits_promotion_promoPosition30,
if(size(hits.promotion) > 31, hits.promotion[31].promoId, null) hits_promotion_promoId31,
if(size(hits.promotion) > 31, hits.promotion[31].promoName, null) hits_promotion_promoName31,
if(size(hits.promotion) > 31, hits.promotion[31].promoCreative, null) hits_promotion_promoCreative31,
if(size(hits.promotion) > 31, hits.promotion[31].promoPosition, null) hits_promotion_promoPosition31,
if(size(hits.promotion) > 32, hits.promotion[32].promoId, null) hits_promotion_promoId32,
if(size(hits.promotion) > 32, hits.promotion[32].promoName, null) hits_promotion_promoName32,
if(size(hits.promotion) > 32, hits.promotion[32].promoCreative, null) hits_promotion_promoCreative32,
if(size(hits.promotion) > 32, hits.promotion[32].promoPosition, null) hits_promotion_promoPosition32,
if(size(hits.promotion) > 33, hits.promotion[33].promoId, null) hits_promotion_promoId33,
if(size(hits.promotion) > 33, hits.promotion[33].promoName, null) hits_promotion_promoName33,
if(size(hits.promotion) > 33, hits.promotion[33].promoCreative, null) hits_promotion_promoCreative33,
if(size(hits.promotion) > 33, hits.promotion[33].promoPosition, null) hits_promotion_promoPosition33,
if(size(hits.promotion) > 34, hits.promotion[34].promoId, null) hits_promotion_promoId34,
if(size(hits.promotion) > 34, hits.promotion[34].promoName, null) hits_promotion_promoName34,
if(size(hits.promotion) > 34, hits.promotion[34].promoCreative, null) hits_promotion_promoCreative34,
if(size(hits.promotion) > 34, hits.promotion[34].promoPosition, null) hits_promotion_promoPosition34,
if(size(hits.promotion) > 35, hits.promotion[35].promoId, null) hits_promotion_promoId35,
if(size(hits.promotion) > 35, hits.promotion[35].promoName, null) hits_promotion_promoName35,
if(size(hits.promotion) > 35, hits.promotion[35].promoCreative, null) hits_promotion_promoCreative35,
if(size(hits.promotion) > 35, hits.promotion[35].promoPosition, null) hits_promotion_promoPosition35,
if(size(hits.promotion) > 36, hits.promotion[36].promoId, null) hits_promotion_promoId36,
if(size(hits.promotion) > 36, hits.promotion[36].promoName, null) hits_promotion_promoName36,
if(size(hits.promotion) > 36, hits.promotion[36].promoCreative, null) hits_promotion_promoCreative36,
if(size(hits.promotion) > 36, hits.promotion[36].promoPosition, null) hits_promotion_promoPosition36,
if(size(hits.promotion) > 37, hits.promotion[37].promoId, null) hits_promotion_promoId37,
if(size(hits.promotion) > 37, hits.promotion[37].promoName, null) hits_promotion_promoName37,
if(size(hits.promotion) > 37, hits.promotion[37].promoCreative, null) hits_promotion_promoCreative37,
if(size(hits.promotion) > 37, hits.promotion[37].promoPosition, null) hits_promotion_promoPosition37,
if(size(hits.promotion) > 38, hits.promotion[38].promoId, null) hits_promotion_promoId38,
if(size(hits.promotion) > 38, hits.promotion[38].promoName, null) hits_promotion_promoName38,
if(size(hits.promotion) > 38, hits.promotion[38].promoCreative, null) hits_promotion_promoCreative38,
if(size(hits.promotion) > 38, hits.promotion[38].promoPosition, null) hits_promotion_promoPosition38,
if(size(hits.promotion) > 39, hits.promotion[39].promoId, null) hits_promotion_promoId39,
if(size(hits.promotion) > 39, hits.promotion[39].promoName, null) hits_promotion_promoName39,
if(size(hits.promotion) > 39, hits.promotion[39].promoCreative, null) hits_promotion_promoCreative39,
if(size(hits.promotion) > 39, hits.promotion[39].promoPosition, null) hits_promotion_promoPosition39,
if(array_contains(hits.customDimensions.index, 0), hits.customDimensions.value[array_position(hits.customDimensions.index, 0) - 1], null) hits_customDimensions0,
if(array_contains(hits.customDimensions.index, 1), hits.customDimensions.value[array_position(hits.customDimensions.index, 1) - 1], null) hits_customDimensions1,
if(array_contains(hits.customDimensions.index, 2), hits.customDimensions.value[array_position(hits.customDimensions.index, 2) - 1], null) hits_customDimensions2,
if(array_contains(hits.customDimensions.index, 3), hits.customDimensions.value[array_position(hits.customDimensions.index, 3) - 1], null) hits_customDimensions3,
if(array_contains(hits.customDimensions.index, 4), hits.customDimensions.value[array_position(hits.customDimensions.index, 4) - 1], null) hits_customDimensions4,
if(array_contains(hits.customDimensions.index, 5), hits.customDimensions.value[array_position(hits.customDimensions.index, 5) - 1], null) hits_customDimensions5,
if(array_contains(hits.customDimensions.index, 6), hits.customDimensions.value[array_position(hits.customDimensions.index, 6) - 1], null) hits_customDimensions6,
if(array_contains(hits.customDimensions.index, 7), hits.customDimensions.value[array_position(hits.customDimensions.index, 7) - 1], null) hits_customDimensions7,
if(array_contains(hits.customDimensions.index, 8), hits.customDimensions.value[array_position(hits.customDimensions.index, 8) - 1], null) hits_customDimensions8,
if(array_contains(hits.customDimensions.index, 9), hits.customDimensions.value[array_position(hits.customDimensions.index, 9) - 1], null) hits_customDimensions9,
if(array_contains(hits.customDimensions.index, 10), hits.customDimensions.value[array_position(hits.customDimensions.index, 10) - 1], null) hits_customDimensions10,
if(array_contains(hits.customDimensions.index, 11), hits.customDimensions.value[array_position(hits.customDimensions.index, 11) - 1], null) hits_customDimensions11,
if(array_contains(hits.customDimensions.index, 12), hits.customDimensions.value[array_position(hits.customDimensions.index, 12) - 1], null) hits_customDimensions12,
if(array_contains(hits.customDimensions.index, 13), hits.customDimensions.value[array_position(hits.customDimensions.index, 13) - 1], null) hits_customDimensions13,
if(array_contains(hits.customDimensions.index, 14), hits.customDimensions.value[array_position(hits.customDimensions.index, 14) - 1], null) hits_customDimensions14,
if(array_contains(hits.customDimensions.index, 15), hits.customDimensions.value[array_position(hits.customDimensions.index, 15) - 1], null) hits_customDimensions15,
if(array_contains(hits.customDimensions.index, 16), hits.customDimensions.value[array_position(hits.customDimensions.index, 16) - 1], null) hits_customDimensions16,
if(array_contains(hits.customDimensions.index, 17), hits.customDimensions.value[array_position(hits.customDimensions.index, 17) - 1], null) hits_customDimensions17,
if(array_contains(hits.customDimensions.index, 18), hits.customDimensions.value[array_position(hits.customDimensions.index, 18) - 1], null) hits_customDimensions18,
if(array_contains(hits.customDimensions.index, 19), hits.customDimensions.value[array_position(hits.customDimensions.index, 19) - 1], null) hits_customDimensions19,
if(array_contains(hits.customDimensions.index, 20), hits.customDimensions.value[array_position(hits.customDimensions.index, 20) - 1], null) hits_customDimensions20,
if(array_contains(hits.customDimensions.index, 21), hits.customDimensions.value[array_position(hits.customDimensions.index, 21) - 1], null) hits_customDimensions21,
if(array_contains(hits.customDimensions.index, 22), hits.customDimensions.value[array_position(hits.customDimensions.index, 22) - 1], null) hits_customDimensions22,
if(array_contains(hits.customDimensions.index, 23), hits.customDimensions.value[array_position(hits.customDimensions.index, 23) - 1], null) hits_customDimensions23,
if(array_contains(hits.customDimensions.index, 24), hits.customDimensions.value[array_position(hits.customDimensions.index, 24) - 1], null) hits_customDimensions24,
if(array_contains(hits.customDimensions.index, 25), hits.customDimensions.value[array_position(hits.customDimensions.index, 25) - 1], null) hits_customDimensions25,
if(array_contains(hits.customDimensions.index, 26), hits.customDimensions.value[array_position(hits.customDimensions.index, 26) - 1], null) hits_customDimensions26,
if(array_contains(hits.customDimensions.index, 27), hits.customDimensions.value[array_position(hits.customDimensions.index, 27) - 1], null) hits_customDimensions27,
if(array_contains(hits.customDimensions.index, 28), hits.customDimensions.value[array_position(hits.customDimensions.index, 28) - 1], null) hits_customDimensions28,
if(array_contains(hits.customDimensions.index, 29), hits.customDimensions.value[array_position(hits.customDimensions.index, 29) - 1], null) hits_customDimensions29,
if(array_contains(hits.customDimensions.index, 30), hits.customDimensions.value[array_position(hits.customDimensions.index, 30) - 1], null) hits_customDimensions30,
if(array_contains(hits.customDimensions.index, 31), hits.customDimensions.value[array_position(hits.customDimensions.index, 31) - 1], null) hits_customDimensions31,
if(array_contains(hits.customDimensions.index, 32), hits.customDimensions.value[array_position(hits.customDimensions.index, 32) - 1], null) hits_customDimensions32,
if(array_contains(hits.customDimensions.index, 33), hits.customDimensions.value[array_position(hits.customDimensions.index, 33) - 1], null) hits_customDimensions33,
if(array_contains(hits.customDimensions.index, 34), hits.customDimensions.value[array_position(hits.customDimensions.index, 34) - 1], null) hits_customDimensions34,
if(array_contains(hits.customDimensions.index, 35), hits.customDimensions.value[array_position(hits.customDimensions.index, 35) - 1], null) hits_customDimensions35,
if(array_contains(hits.customDimensions.index, 36), hits.customDimensions.value[array_position(hits.customDimensions.index, 36) - 1], null) hits_customDimensions36,
if(array_contains(hits.customDimensions.index, 37), hits.customDimensions.value[array_position(hits.customDimensions.index, 37) - 1], null) hits_customDimensions37,
if(array_contains(hits.customDimensions.index, 38), hits.customDimensions.value[array_position(hits.customDimensions.index, 38) - 1], null) hits_customDimensions38,
if(array_contains(hits.customDimensions.index, 39), hits.customDimensions.value[array_position(hits.customDimensions.index, 39) - 1], null) hits_customDimensions39,
if(array_contains(hits.customMetrics.index, 0), hits.customMetrics.value[array_position(hits.customMetrics.index, 0) - 1], null) hits_customMetrics0,
if(array_contains(hits.customMetrics.index, 1), hits.customMetrics.value[array_position(hits.customMetrics.index, 1) - 1], null) hits_customMetrics1,
if(array_contains(hits.customMetrics.index, 2), hits.customMetrics.value[array_position(hits.customMetrics.index, 2) - 1], null) hits_customMetrics2,
if(array_contains(hits.customMetrics.index, 3), hits.customMetrics.value[array_position(hits.customMetrics.index, 3) - 1], null) hits_customMetrics3,
if(array_contains(hits.customMetrics.index, 4), hits.customMetrics.value[array_position(hits.customMetrics.index, 4) - 1], null) hits_customMetrics4,
if(array_contains(hits.customMetrics.index, 5), hits.customMetrics.value[array_position(hits.customMetrics.index, 5) - 1], null) hits_customMetrics5,
if(array_contains(hits.customMetrics.index, 6), hits.customMetrics.value[array_position(hits.customMetrics.index, 6) - 1], null) hits_customMetrics6,
if(array_contains(hits.customMetrics.index, 7), hits.customMetrics.value[array_position(hits.customMetrics.index, 7) - 1], null) hits_customMetrics7,
if(array_contains(hits.customMetrics.index, 8), hits.customMetrics.value[array_position(hits.customMetrics.index, 8) - 1], null) hits_customMetrics8,
if(array_contains(hits.customMetrics.index, 9), hits.customMetrics.value[array_position(hits.customMetrics.index, 9) - 1], null) hits_customMetrics9,
if(array_contains(hits.customMetrics.index, 10), hits.customMetrics.value[array_position(hits.customMetrics.index, 10) - 1], null) hits_customMetrics10,
if(array_contains(hits.customMetrics.index, 11), hits.customMetrics.value[array_position(hits.customMetrics.index, 11) - 1], null) hits_customMetrics11,
if(array_contains(hits.customMetrics.index, 12), hits.customMetrics.value[array_position(hits.customMetrics.index, 12) - 1], null) hits_customMetrics12,
if(array_contains(hits.customMetrics.index, 13), hits.customMetrics.value[array_position(hits.customMetrics.index, 13) - 1], null) hits_customMetrics13,
if(array_contains(hits.customMetrics.index, 14), hits.customMetrics.value[array_position(hits.customMetrics.index, 14) - 1], null) hits_customMetrics14,
if(array_contains(hits.customMetrics.index, 15), hits.customMetrics.value[array_position(hits.customMetrics.index, 15) - 1], null) hits_customMetrics15,
if(array_contains(hits.customMetrics.index, 16), hits.customMetrics.value[array_position(hits.customMetrics.index, 16) - 1], null) hits_customMetrics16,
if(array_contains(hits.customMetrics.index, 17), hits.customMetrics.value[array_position(hits.customMetrics.index, 17) - 1], null) hits_customMetrics17,
if(array_contains(hits.customMetrics.index, 18), hits.customMetrics.value[array_position(hits.customMetrics.index, 18) - 1], null) hits_customMetrics18,
if(array_contains(hits.customMetrics.index, 19), hits.customMetrics.value[array_position(hits.customMetrics.index, 19) - 1], null) hits_customMetrics19,
if(array_contains(hits.customMetrics.index, 20), hits.customMetrics.value[array_position(hits.customMetrics.index, 20) - 1], null) hits_customMetrics20,
if(array_contains(hits.customMetrics.index, 21), hits.customMetrics.value[array_position(hits.customMetrics.index, 21) - 1], null) hits_customMetrics21,
if(array_contains(hits.customMetrics.index, 22), hits.customMetrics.value[array_position(hits.customMetrics.index, 22) - 1], null) hits_customMetrics22,
if(array_contains(hits.customMetrics.index, 23), hits.customMetrics.value[array_position(hits.customMetrics.index, 23) - 1], null) hits_customMetrics23,
if(array_contains(hits.customMetrics.index, 24), hits.customMetrics.value[array_position(hits.customMetrics.index, 24) - 1], null) hits_customMetrics24,
if(array_contains(hits.customMetrics.index, 25), hits.customMetrics.value[array_position(hits.customMetrics.index, 25) - 1], null) hits_customMetrics25,
if(array_contains(hits.customMetrics.index, 26), hits.customMetrics.value[array_position(hits.customMetrics.index, 26) - 1], null) hits_customMetrics26,
if(array_contains(hits.customMetrics.index, 27), hits.customMetrics.value[array_position(hits.customMetrics.index, 27) - 1], null) hits_customMetrics27,
if(array_contains(hits.customMetrics.index, 28), hits.customMetrics.value[array_position(hits.customMetrics.index, 28) - 1], null) hits_customMetrics28,
if(array_contains(hits.customMetrics.index, 29), hits.customMetrics.value[array_position(hits.customMetrics.index, 29) - 1], null) hits_customMetrics29,
if(array_contains(hits.customMetrics.index, 30), hits.customMetrics.value[array_position(hits.customMetrics.index, 30) - 1], null) hits_customMetrics30,
if(array_contains(hits.customMetrics.index, 31), hits.customMetrics.value[array_position(hits.customMetrics.index, 31) - 1], null) hits_customMetrics31,
if(array_contains(hits.customMetrics.index, 32), hits.customMetrics.value[array_position(hits.customMetrics.index, 32) - 1], null) hits_customMetrics32,
if(array_contains(hits.customMetrics.index, 33), hits.customMetrics.value[array_position(hits.customMetrics.index, 33) - 1], null) hits_customMetrics33,
if(array_contains(hits.customMetrics.index, 34), hits.customMetrics.value[array_position(hits.customMetrics.index, 34) - 1], null) hits_customMetrics34,
if(array_contains(hits.customMetrics.index, 35), hits.customMetrics.value[array_position(hits.customMetrics.index, 35) - 1], null) hits_customMetrics35,
if(array_contains(hits.customMetrics.index, 36), hits.customMetrics.value[array_position(hits.customMetrics.index, 36) - 1], null) hits_customMetrics36,
if(array_contains(hits.customMetrics.index, 37), hits.customMetrics.value[array_position(hits.customMetrics.index, 37) - 1], null) hits_customMetrics37,
if(array_contains(hits.customMetrics.index, 38), hits.customMetrics.value[array_position(hits.customMetrics.index, 38) - 1], null) hits_customMetrics38,
if(array_contains(hits.customMetrics.index, 39), hits.customMetrics.value[array_position(hits.customMetrics.index, 39) - 1], null) hits_customMetrics39,
if(array_contains(customDimensions.index, 0), customDimensions.value[array_position(customDimensions.index, 0) - 1], null) cd0,
if(array_contains(customDimensions.index, 1), customDimensions.value[array_position(customDimensions.index, 1) - 1], null) cd1,
if(array_contains(customDimensions.index, 2), customDimensions.value[array_position(customDimensions.index, 2) - 1], null) cd2,
if(array_contains(customDimensions.index, 3), customDimensions.value[array_position(customDimensions.index, 3) - 1], null) cd3,
if(array_contains(customDimensions.index, 4), customDimensions.value[array_position(customDimensions.index, 4) - 1], null) cd4,
if(array_contains(customDimensions.index, 5), customDimensions.value[array_position(customDimensions.index, 5) - 1], null) cd5,
if(array_contains(customDimensions.index, 6), customDimensions.value[array_position(customDimensions.index, 6) - 1], null) cd6,
if(array_contains(customDimensions.index, 7), customDimensions.value[array_position(customDimensions.index, 7) - 1], null) cd7,
if(array_contains(customDimensions.index, 8), customDimensions.value[array_position(customDimensions.index, 8) - 1], null) cd8,
if(array_contains(customDimensions.index, 9), customDimensions.value[array_position(customDimensions.index, 9) - 1], null) cd9,
if(array_contains(customDimensions.index, 10), customDimensions.value[array_position(customDimensions.index, 10) - 1], null) cd10,
if(array_contains(customDimensions.index, 11), customDimensions.value[array_position(customDimensions.index, 11) - 1], null) cd11,
if(array_contains(customDimensions.index, 12), customDimensions.value[array_position(customDimensions.index, 12) - 1], null) cd12,
if(array_contains(customDimensions.index, 13), customDimensions.value[array_position(customDimensions.index, 13) - 1], null) cd13,
if(array_contains(customDimensions.index, 14), customDimensions.value[array_position(customDimensions.index, 14) - 1], null) cd14,
if(array_contains(customDimensions.index, 15), customDimensions.value[array_position(customDimensions.index, 15) - 1], null) cd15,
if(array_contains(customDimensions.index, 16), customDimensions.value[array_position(customDimensions.index, 16) - 1], null) cd16,
if(array_contains(customDimensions.index, 17), customDimensions.value[array_position(customDimensions.index, 17) - 1], null) cd17,
if(array_contains(customDimensions.index, 18), customDimensions.value[array_position(customDimensions.index, 18) - 1], null) cd18,
if(array_contains(customDimensions.index, 19), customDimensions.value[array_position(customDimensions.index, 19) - 1], null) cd19,
if(array_contains(customDimensions.index, 20), customDimensions.value[array_position(customDimensions.index, 20) - 1], null) cd20,
if(array_contains(customDimensions.index, 21), customDimensions.value[array_position(customDimensions.index, 21) - 1], null) cd21,
if(array_contains(customDimensions.index, 22), customDimensions.value[array_position(customDimensions.index, 22) - 1], null) cd22,
if(array_contains(customDimensions.index, 23), customDimensions.value[array_position(customDimensions.index, 23) - 1], null) cd23,
if(array_contains(customDimensions.index, 24), customDimensions.value[array_position(customDimensions.index, 24) - 1], null) cd24,
if(array_contains(customDimensions.index, 25), customDimensions.value[array_position(customDimensions.index, 25) - 1], null) cd25,
if(array_contains(customDimensions.index, 26), customDimensions.value[array_position(customDimensions.index, 26) - 1], null) cd26,
if(array_contains(customDimensions.index, 27), customDimensions.value[array_position(customDimensions.index, 27) - 1], null) cd27,
if(array_contains(customDimensions.index, 28), customDimensions.value[array_position(customDimensions.index, 28) - 1], null) cd28,
if(array_contains(customDimensions.index, 29), customDimensions.value[array_position(customDimensions.index, 29) - 1], null) cd29,
if(array_contains(customDimensions.index, 30), customDimensions.value[array_position(customDimensions.index, 30) - 1], null) cd30,
if(array_contains(customDimensions.index, 31), customDimensions.value[array_position(customDimensions.index, 31) - 1], null) cd31,
if(array_contains(customDimensions.index, 32), customDimensions.value[array_position(customDimensions.index, 32) - 1], null) cd32,
if(array_contains(customDimensions.index, 33), customDimensions.value[array_position(customDimensions.index, 33) - 1], null) cd33,
if(array_contains(customDimensions.index, 34), customDimensions.value[array_position(customDimensions.index, 34) - 1], null) cd34,
if(array_contains(customDimensions.index, 35), customDimensions.value[array_position(customDimensions.index, 35) - 1], null) cd35,
if(array_contains(customDimensions.index, 36), customDimensions.value[array_position(customDimensions.index, 36) - 1], null) cd36,
if(array_contains(customDimensions.index, 37), customDimensions.value[array_position(customDimensions.index, 37) - 1], null) cd37,
if(array_contains(customDimensions.index, 38), customDimensions.value[array_position(customDimensions.index, 38) - 1], null) cd38,
if(array_contains(customDimensions.index, 39), customDimensions.value[array_position(customDimensions.index, 39) - 1], null) cd39,
if(array_contains(customDimensions.index, 40), customDimensions.value[array_position(customDimensions.index, 40) - 1], null) cd40,
if(array_contains(customDimensions.index, 41), customDimensions.value[array_position(customDimensions.index, 41) - 1], null) cd41,
if(array_contains(customDimensions.index, 42), customDimensions.value[array_position(customDimensions.index, 42) - 1], null) cd42,
if(array_contains(customDimensions.index, 43), customDimensions.value[array_position(customDimensions.index, 43) - 1], null) cd43,
if(array_contains(customDimensions.index, 44), customDimensions.value[array_position(customDimensions.index, 44) - 1], null) cd44,
if(array_contains(customDimensions.index, 45), customDimensions.value[array_position(customDimensions.index, 45) - 1], null) cd45,
if(array_contains(customDimensions.index, 46), customDimensions.value[array_position(customDimensions.index, 46) - 1], null) cd46,
if(array_contains(customDimensions.index, 47), customDimensions.value[array_position(customDimensions.index, 47) - 1], null) cd47,
if(array_contains(customDimensions.index, 48), customDimensions.value[array_position(customDimensions.index, 48) - 1], null) cd48,
if(array_contains(customDimensions.index, 49), customDimensions.value[array_position(customDimensions.index, 49) - 1], null) cd49,
if(array_contains(customDimensions.index, 50), customDimensions.value[array_position(customDimensions.index, 50) - 1], null) cd50,
if(array_contains(customDimensions.index, 51), customDimensions.value[array_position(customDimensions.index, 51) - 1], null) cd51,
if(array_contains(customDimensions.index, 52), customDimensions.value[array_position(customDimensions.index, 52) - 1], null) cd52,
if(array_contains(customDimensions.index, 53), customDimensions.value[array_position(customDimensions.index, 53) - 1], null) cd53,
if(array_contains(customDimensions.index, 54), customDimensions.value[array_position(customDimensions.index, 54) - 1], null) cd54,
if(array_contains(customDimensions.index, 55), customDimensions.value[array_position(customDimensions.index, 55) - 1], null) cd55,
if(array_contains(customDimensions.index, 56), customDimensions.value[array_position(customDimensions.index, 56) - 1], null) cd56,
if(array_contains(customDimensions.index, 57), customDimensions.value[array_position(customDimensions.index, 57) - 1], null) cd57,
if(array_contains(customDimensions.index, 58), customDimensions.value[array_position(customDimensions.index, 58) - 1], null) cd58,
if(array_contains(customDimensions.index, 59), customDimensions.value[array_position(customDimensions.index, 59) - 1], null) cd59,
if(array_contains(customDimensions.index, 60), customDimensions.value[array_position(customDimensions.index, 60) - 1], null) cd60,
if(array_contains(customDimensions.index, 61), customDimensions.value[array_position(customDimensions.index, 61) - 1], null) cd61,
if(array_contains(customDimensions.index, 62), customDimensions.value[array_position(customDimensions.index, 62) - 1], null) cd62,
if(array_contains(customDimensions.index, 63), customDimensions.value[array_position(customDimensions.index, 63) - 1], null) cd63,
if(array_contains(customDimensions.index, 64), customDimensions.value[array_position(customDimensions.index, 64) - 1], null) cd64,
if(array_contains(customDimensions.index, 65), customDimensions.value[array_position(customDimensions.index, 65) - 1], null) cd65,
if(array_contains(customDimensions.index, 66), customDimensions.value[array_position(customDimensions.index, 66) - 1], null) cd66,
if(array_contains(customDimensions.index, 67), customDimensions.value[array_position(customDimensions.index, 67) - 1], null) cd67,
if(array_contains(customDimensions.index, 68), customDimensions.value[array_position(customDimensions.index, 68) - 1], null) cd68,
if(array_contains(customDimensions.index, 69), customDimensions.value[array_position(customDimensions.index, 69) - 1], null) cd69,
if(array_contains(customDimensions.index, 70), customDimensions.value[array_position(customDimensions.index, 70) - 1], null) cd70,
if(array_contains(customDimensions.index, 71), customDimensions.value[array_position(customDimensions.index, 71) - 1], null) cd71,
if(array_contains(customDimensions.index, 72), customDimensions.value[array_position(customDimensions.index, 72) - 1], null) cd72,
if(array_contains(customDimensions.index, 73), customDimensions.value[array_position(customDimensions.index, 73) - 1], null) cd73,
if(array_contains(customDimensions.index, 74), customDimensions.value[array_position(customDimensions.index, 74) - 1], null) cd74,
if(array_contains(customDimensions.index, 75), customDimensions.value[array_position(customDimensions.index, 75) - 1], null) cd75,
if(array_contains(customDimensions.index, 76), customDimensions.value[array_position(customDimensions.index, 76) - 1], null) cd76,
if(array_contains(customDimensions.index, 77), customDimensions.value[array_position(customDimensions.index, 77) - 1], null) cd77,
if(array_contains(customDimensions.index, 78), customDimensions.value[array_position(customDimensions.index, 78) - 1], null) cd78,
if(array_contains(customDimensions.index, 79), customDimensions.value[array_position(customDimensions.index, 79) - 1], null) cd79,
if(array_contains(customDimensions.index, 80), customDimensions.value[array_position(customDimensions.index, 80) - 1], null) cd80,
event_partition
FROM
@InputTable1
WHERE
event_partition between @fromDate and @toDate
""")
spark.sql("""
CREATE TABLE IF NOT EXISTS @OutputTable1
(visitorId long,
visitNumber long,
visitId long,
visitStartTime long,
date string,
totals_bounces long,
totals_hits long,
totals_newVisits long,
totals_pageviews long,
totals_screenviews long,
totals_sessionQualityDim long,
totals_timeOnScreen long,
totals_timeOnSite long,
totals_totalTransactionRevenue long,
totals_transactionRevenue long,
totals_transactions long,
totals_UniqueScreenViews long,
totals_visits long,
productbrand string,
v2productcategory string,
v2productname string,
click_product_lists string,
product_detail_views string,
cart_add string,
cart_remove string,
checkout string,
completed_purchase string,
refund string,
checkout_options string,
trafficSource_referralPath string,
trafficSource_campaign string,
trafficSource_source string,
trafficSource_medium string,
trafficSource_keyword string,
trafficSource_adContent string,
trafficSource_adwordsClickInfo_campaignId long,
trafficSource_adwordsClickInfo_adGroupId long,
trafficSource_adwordsClickInfo_creativeId long,
trafficSource_adwordsClickInfo_criteriaId long,
trafficSource_adwordsClickInfo_page long,
trafficSource_adwordsClickInfo_slot string,
trafficSource_adwordsClickInfo_criteriaParameters string,
trafficSource_adwordsClickInfo_gclId string,
trafficSource_adwordsClickInfo_customerId long,
trafficSource_adwordsClickInfo_adNetworkType string,
trafficSource_adwordsClickInfo_targetingCriteria_boomUserlistId long,
trafficSource_adwordsClickInfo_isVideoAd boolean,
trafficSource_isTrueDirect boolean,
trafficSource_campaignCode string,
device_browser string,
device_browserVersion string,
device_browserSize string,
device_operatingSystem string,
device_operatingSystemVersion string,
device_isMobile boolean,
device_mobileDeviceBranding string,
device_mobileDeviceModel string,
device_mobileInputSelector string,
device_mobileDeviceInfo string,
device_mobileDeviceMarketingName string,
device_flashVersion string,
device_javaEnabled boolean,
device_language string,
device_screenColors string,
device_screenResolution string,
device_deviceCategory string,
geoNetwork_continent string,
geoNetwork_subContinent string,
geoNetwork_country string,
geoNetwork_region string,
geoNetwork_metro string,
geoNetwork_city string,
geoNetwork_cityId string,
geoNetwork_networkDomain string,
geoNetwork_latitude string,
geoNetwork_longitude string,
geoNetwork_networkLocation string,
hits_hitNumber long,
hits_time long,
hits_hour long,
hits_minute long,
hits_isSecure boolean,
hits_isInteraction boolean,
hits_isEntrance boolean,
hits_isExit boolean,
hits_referer string,
hits_page_pagePath string,
hits_page_hostname string,
hits_page_pageTitle string,
hits_page_searchKeyword string,
hits_page_searchCategory string,
hits_page_pagePathLevel1 string,
hits_page_pagePathLevel2 string,
hits_page_pagePathLevel3 string,
hits_page_pagePathLevel4 string,
hits_transaction_transactionId string,
hits_transaction_transactionRevenue long,
hits_transaction_transactionTax long,
hits_transaction_transactionShipping long,
hits_transaction_affiliation string,
hits_transaction_currencyCode string,
hits_transaction_localTransactionRevenue long,
hits_transaction_localTransactionTax long,
hits_transaction_localTransactionShipping long,
hits_transaction_transactionCoupon string,
hits_item_transactionId string,
hits_item_productName string,
hits_item_productCategory string,
hits_item_productSku string,
hits_item_itemQuantity long,
hits_item_itemRevenue long,
hits_item_currencyCode string,
hits_item_localItemRevenue long,
hits_contentInfo_contentDescription string,
hits_appInfo_name string,
hits_appInfo_version string,
hits_appInfo_id string,
hits_appInfo_installerId string,
hits_appInfo_appInstallerId string,
hits_appInfo_appName string,
hits_appInfo_appVersion string,
hits_appInfo_appId string,
hits_appInfo_screenName string,
hits_appInfo_landingScreenName string,
hits_appInfo_exitScreenName string,
hits_appInfo_screenDepth string,
hits_exceptionInfo_description string,
hits_exceptionInfo_isFatal boolean,
hits_exceptionInfo_exceptions long,
hits_exceptionInfo_fatalExceptions long,
hits_eventInfo_eventCategory string,
hits_eventInfo_eventAction string,
hits_eventInfo_eventLabel string,
hits_eventInfo_eventValue long,
hits_promotionActionInfo_promoIsView boolean,
hits_promotionActionInfo_promoIsClick boolean,
hits_refund_refundAmount long,
hits_refund_localRefundAmount long,
hits_eCommerceAction_action_type string,
hits_eCommerceAction_step long,
hits_eCommerceAction_option string,
hits_publisher_dfpClicks long,
hits_publisher_dfpImpressions long,
hits_publisher_dfpMatchedQueries long,
hits_publisher_dfpMeasurableImpressions long,
hits_publisher_dfpQueries long,
hits_publisher_dfpRevenueCpm long,
hits_publisher_dfpRevenueCpc long,
hits_publisher_dfpViewableImpressions long,
hits_publisher_dfpPagesViewed long,
hits_publisher_adsenseBackfillDfpClicks long,
hits_publisher_adsenseBackfillDfpImpressions long,
hits_publisher_adsenseBackfillDfpMatchedQueries long,
hits_publisher_adsenseBackfillDfpMeasurableImpressions long,
hits_publisher_adsenseBackfillDfpQueries long,
hits_publisher_adsenseBackfillDfpRevenueCpm long,
hits_publisher_adsenseBackfillDfpRevenueCpc long,
hits_publisher_adsenseBackfillDfpViewableImpressions long,
hits_publisher_adsenseBackfillDfpPagesViewed long,
hits_publisher_adxBackfillDfpClicks long,
hits_publisher_adxBackfillDfpImpressions long,
hits_publisher_adxBackfillDfpMatchedQueries long,
hits_publisher_adxBackfillDfpMeasurableImpressions long,
hits_publisher_adxBackfillDfpQueries long,
hits_publisher_adxBackfillDfpRevenueCpm long,
hits_publisher_adxBackfillDfpRevenueCpc long,
hits_publisher_adxBackfillDfpViewableImpressions long,
hits_publisher_adxBackfillDfpPagesViewed long,
hits_publisher_adxClicks long,
hits_publisher_adxImpressions long,
hits_publisher_adxMatchedQueries long,
hits_publisher_adxMeasurableImpressions long,
hits_publisher_adxQueries long,
hits_publisher_adxRevenue long,
hits_publisher_adxViewableImpressions long,
hits_publisher_adxPagesViewed long,
hits_publisher_adsViewed long,
hits_publisher_adsUnitsViewed long,
hits_publisher_adsUnitsMatched long,
hits_publisher_viewableAdsViewed long,
hits_publisher_measurableAdsViewed long,
hits_publisher_adsPagesViewed long,
hits_publisher_adsClicked long,
hits_publisher_adsRevenue long,
hits_publisher_dfpAdGroup string,
hits_publisher_dfpAdUnits string,
hits_publisher_dfpNetworkId string,
hits_type string,
hits_social_socialInteractionNetwork string,
hits_social_socialInteractionAction string,
hits_social_socialInteractions long,
hits_social_socialInteractionTarget string,
hits_social_socialNetwork string,
hits_social_uniqueSocialInteractions long,
hits_social_hasSocialSourceReferral string,
hits_social_socialInteractionNetworkAction string,
hits_latencyTracking_pageLoadSample long,
hits_latencyTracking_pageLoadTime long,
hits_latencyTracking_pageDownloadTime long,
hits_latencyTracking_redirectionTime long,
hits_latencyTracking_speedMetricsSample long,
hits_latencyTracking_domainLookupTime long,
hits_latencyTracking_serverConnectionTime long,
hits_latencyTracking_serverResponseTime long,
hits_latencyTracking_domLatencyMetricsSample long,
hits_latencyTracking_domInteractiveTime long,
hits_latencyTracking_domContentLoadedTime long,
hits_latencyTracking_userTimingValue long,
hits_latencyTracking_userTimingSample long,
hits_latencyTracking_userTimingVariable string,
hits_latencyTracking_userTimingCategory string,
hits_latencyTracking_userTimingLabel string,
hits_sourcePropertyInfo_sourcePropertyDisplayName string,
hits_sourcePropertyInfo_sourcePropertyTrackingId string,
hits_contentGroup_contentGroup1 string,
hits_contentGroup_contentGroup2 string,
hits_contentGroup_contentGroup3 string,
hits_contentGroup_contentGroup4 string,
hits_contentGroup_contentGroup5 string,
hits_contentGroup_previousContentGroup1 string,
hits_contentGroup_previousContentGroup2 string,
hits_contentGroup_previousContentGroup3 string,
hits_contentGroup_previousContentGroup4 string,
hits_contentGroup_previousContentGroup5 string,
hits_contentGroup_contentGroupUniqueViews1 long,
hits_contentGroup_contentGroupUniqueViews2 long,
hits_contentGroup_contentGroupUniqueViews3 long,
hits_contentGroup_contentGroupUniqueViews4 long,
hits_contentGroup_contentGroupUniqueViews5 long,
hits_dataSource string,
fullVisitorId string,
userId string,
clientId string,
channelGrouping string,
socialEngagementType string,
hits_promotion_promoId0 string,
hits_promotion_promoName0 string,
hits_promotion_promoCreative0 string,
hits_promotion_promoPosition0 string,
hits_promotion_promoId1 string,
hits_promotion_promoName1 string,
hits_promotion_promoCreative1 string,
hits_promotion_promoPosition1 string,
hits_promotion_promoId2 string,
hits_promotion_promoName2 string,
hits_promotion_promoCreative2 string,
hits_promotion_promoPosition2 string,
hits_promotion_promoId3 string,
hits_promotion_promoName3 string,
hits_promotion_promoCreative3 string,
hits_promotion_promoPosition3 string,
hits_promotion_promoId4 string,
hits_promotion_promoName4 string,
hits_promotion_promoCreative4 string,
hits_promotion_promoPosition4 string,
hits_promotion_promoId5 string,
hits_promotion_promoName5 string,
hits_promotion_promoCreative5 string,
hits_promotion_promoPosition5 string,
hits_promotion_promoId6 string,
hits_promotion_promoName6 string,
hits_promotion_promoCreative6 string,
hits_promotion_promoPosition6 string,
hits_promotion_promoId7 string,
hits_promotion_promoName7 string,
hits_promotion_promoCreative7 string,
hits_promotion_promoPosition7 string,
hits_promotion_promoId8 string,
hits_promotion_promoName8 string,
hits_promotion_promoCreative8 string,
hits_promotion_promoPosition8 string,
hits_promotion_promoId9 string,
hits_promotion_promoName9 string,
hits_promotion_promoCreative9 string,
hits_promotion_promoPosition9 string,
hits_promotion_promoId10 string,
hits_promotion_promoName10 string,
hits_promotion_promoCreative10 string,
hits_promotion_promoPosition10 string,
hits_promotion_promoId11 string,
hits_promotion_promoName11 string,
hits_promotion_promoCreative11 string,
hits_promotion_promoPosition11 string,
hits_promotion_promoId12 string,
hits_promotion_promoName12 string,
hits_promotion_promoCreative12 string,
hits_promotion_promoPosition12 string,
hits_promotion_promoId13 string,
hits_promotion_promoName13 string,
hits_promotion_promoCreative13 string,
hits_promotion_promoPosition13 string,
hits_promotion_promoId14 string,
hits_promotion_promoName14 string,
hits_promotion_promoCreative14 string,
hits_promotion_promoPosition14 string,
hits_promotion_promoId15 string,
hits_promotion_promoName15 string,
hits_promotion_promoCreative15 string,
hits_promotion_promoPosition15 string,
hits_promotion_promoId16 string,
hits_promotion_promoName16 string,
hits_promotion_promoCreative16 string,
hits_promotion_promoPosition16 string,
hits_promotion_promoId17 string,
hits_promotion_promoName17 string,
hits_promotion_promoCreative17 string,
hits_promotion_promoPosition17 string,
hits_promotion_promoId18 string,
hits_promotion_promoName18 string,
hits_promotion_promoCreative18 string,
hits_promotion_promoPosition18 string,
hits_promotion_promoId19 string,
hits_promotion_promoName19 string,
hits_promotion_promoCreative19 string,
hits_promotion_promoPosition19 string,
hits_promotion_promoId20 string,
hits_promotion_promoName20 string,
hits_promotion_promoCreative20 string,
hits_promotion_promoPosition20 string,
hits_promotion_promoId21 string,
hits_promotion_promoName21 string,
hits_promotion_promoCreative21 string,
hits_promotion_promoPosition21 string,
hits_promotion_promoId22 string,
hits_promotion_promoName22 string,
hits_promotion_promoCreative22 string,
hits_promotion_promoPosition22 string,
hits_promotion_promoId23 string,
hits_promotion_promoName23 string,
hits_promotion_promoCreative23 string,
hits_promotion_promoPosition23 string,
hits_promotion_promoId24 string,
hits_promotion_promoName24 string,
hits_promotion_promoCreative24 string,
hits_promotion_promoPosition24 string,
hits_promotion_promoId25 string,
hits_promotion_promoName25 string,
hits_promotion_promoCreative25 string,
hits_promotion_promoPosition25 string,
hits_promotion_promoId26 string,
hits_promotion_promoName26 string,
hits_promotion_promoCreative26 string,
hits_promotion_promoPosition26 string,
hits_promotion_promoId27 string,
hits_promotion_promoName27 string,
hits_promotion_promoCreative27 string,
hits_promotion_promoPosition27 string,
hits_promotion_promoId28 string,
hits_promotion_promoName28 string,
hits_promotion_promoCreative28 string,
hits_promotion_promoPosition28 string,
hits_promotion_promoId29 string,
hits_promotion_promoName29 string,
hits_promotion_promoCreative29 string,
hits_promotion_promoPosition29 string,
hits_promotion_promoId30 string,
hits_promotion_promoName30 string,
hits_promotion_promoCreative30 string,
hits_promotion_promoPosition30 string,
hits_promotion_promoId31 string,
hits_promotion_promoName31 string,
hits_promotion_promoCreative31 string,
hits_promotion_promoPosition31 string,
hits_promotion_promoId32 string,
hits_promotion_promoName32 string,
hits_promotion_promoCreative32 string,
hits_promotion_promoPosition32 string,
hits_promotion_promoId33 string,
hits_promotion_promoName33 string,
hits_promotion_promoCreative33 string,
hits_promotion_promoPosition33 string,
hits_promotion_promoId34 string,
hits_promotion_promoName34 string,
hits_promotion_promoCreative34 string,
hits_promotion_promoPosition34 string,
hits_promotion_promoId35 string,
hits_promotion_promoName35 string,
hits_promotion_promoCreative35 string,
hits_promotion_promoPosition35 string,
hits_promotion_promoId36 string,
hits_promotion_promoName36 string,
hits_promotion_promoCreative36 string,
hits_promotion_promoPosition36 string,
hits_promotion_promoId37 string,
hits_promotion_promoName37 string,
hits_promotion_promoCreative37 string,
hits_promotion_promoPosition37 string,
hits_promotion_promoId38 string,
hits_promotion_promoName38 string,
hits_promotion_promoCreative38 string,
hits_promotion_promoPosition38 string,
hits_promotion_promoId39 string,
hits_promotion_promoName39 string,
hits_promotion_promoCreative39 string,
hits_promotion_promoPosition39 string,
hits_customDimensions0 string,
hits_customDimensions1 string,
hits_customDimensions2 string,
hits_customDimensions3 string,
hits_customDimensions4 string,
hits_customDimensions5 string,
hits_customDimensions6 string,
hits_customDimensions7 string,
hits_customDimensions8 string,
hits_customDimensions9 string,
hits_customDimensions10 string,
hits_customDimensions11 string,
hits_customDimensions12 string,
hits_customDimensions13 string,
hits_customDimensions14 string,
hits_customDimensions15 string,
hits_customDimensions16 string,
hits_customDimensions17 string,
hits_customDimensions18 string,
hits_customDimensions19 string,
hits_customDimensions20 string,
hits_customDimensions21 string,
hits_customDimensions22 string,
hits_customDimensions23 string,
hits_customDimensions24 string,
hits_customDimensions25 string,
hits_customDimensions26 string,
hits_customDimensions27 string,
hits_customDimensions28 string,
hits_customDimensions29 string,
hits_customDimensions30 string,
hits_customDimensions31 string,
hits_customDimensions32 string,
hits_customDimensions33 string,
hits_customDimensions34 string,
hits_customDimensions35 string,
hits_customDimensions36 string,
hits_customDimensions37 string,
hits_customDimensions38 string,
hits_customDimensions39 string,
hits_customMetrics0 long,
hits_customMetrics1 long,
hits_customMetrics2 long,
hits_customMetrics3 long,
hits_customMetrics4 long,
hits_customMetrics5 long,
hits_customMetrics6 long,
hits_customMetrics7 long,
hits_customMetrics8 long,
hits_customMetrics9 long,
hits_customMetrics10 long,
hits_customMetrics11 long,
hits_customMetrics12 long,
hits_customMetrics13 long,
hits_customMetrics14 long,
hits_customMetrics15 long,
hits_customMetrics16 long,
hits_customMetrics17 long,
hits_customMetrics18 long,
hits_customMetrics19 long,
hits_customMetrics20 long,
hits_customMetrics21 long,
hits_customMetrics22 long,
hits_customMetrics23 long,
hits_customMetrics24 long,
hits_customMetrics25 long,
hits_customMetrics26 long,
hits_customMetrics27 long,
hits_customMetrics28 long,
hits_customMetrics29 long,
hits_customMetrics30 long,
hits_customMetrics31 long,
hits_customMetrics32 long,
hits_customMetrics33 long,
hits_customMetrics34 long,
hits_customMetrics35 long,
hits_customMetrics36 long,
hits_customMetrics37 long,
hits_customMetrics38 long,
hits_customMetrics39 long,
customDimension0 string,
customDimension1 string,
customDimension2 string,
customDimension3 string,
customDimension4 string,
customDimension5 string,
customDimension6 string,
customDimension7 string,
customDimension8 string,
customDimension9 string,
customDimension10 string,
customDimension11 string,
customDimension12 string,
customDimension13 string,
customDimension14 string,
customDimension15 string,
customDimension16 string,
customDimension17 string,
customDimension18 string,
customDimension19 string,
customDimension20 string,
customDimension21 string,
customDimension22 string,
customDimension23 string,
customDimension24 string,
customDimension25 string,
customDimension26 string,
customDimension27 string,
customDimension28 string,
customDimension29 string,
customDimension30 string,
customDimension31 string,
customDimension32 string,
customDimension33 string,
customDimension34 string,
customDimension35 string,
customDimension36 string,
customDimension37 string,
customDimension38 string,
customDimension39 string,
customDimension40 string,
customDimension41 string,
customDimension42 string,
customDimension43 string,
customDimension44 string,
customDimension45 string,
customDimension46 string,
customDimension47 string,
customDimension48 string,
customDimension49 string,
customDimension50 string,
customDimension51 string,
customDimension52 string,
customDimension53 string,
customDimension54 string,
customDimension55 string,
customDimension56 string,
customDimension57 string,
customDimension58 string,
customDimension59 string,
customDimension60 string,
customDimension61 string,
customDimension62 string,
customDimension63 string,
customDimension64 string,
customDimension65 string,
customDimension66 string,
customDimension67 string,
customDimension68 string,
customDimension69 string,
customDimension70 string,
customDimension71 string,
customDimension72 string,
customDimension73 string,
customDimension74 string,
customDimension75 string,
customDimension76 string,
customDimension77 string,
customDimension78 string,
customDimension79 string,
customDimension80 string)
PARTITIONED BY (event_partition string)
LOCATION @location
STORED AS parquet
""")
spark.sql("SET hive.exec.dynamic.partition.mode=nonstrict")
tb.createOrReplaceTempView("tb")
spark.sql("""
INSERT OVERWRITE TABLE @OutputTable1
SELECT
*
FROM
tb
""")