trino.sql

insert into clickhouse.dbt_bq.dm_owoxbi_sessions
Select 
  user.id as user_id, user.phone as user_phone, user.email as user_email,
  clientId as clientId, date as date, sessionId as sessionId , visitNumber as visitNumber,newVisits as newVisits,
  t.device.deviceCategory as device_category, t.device.browser as device_browser, t.device.browserVersion as device_browser_version,
  t.device.operatingSystem as device_operating_system, t.device.operatingSystemVersion as device_operating_system_version,
  t.device.mobileDeviceInfo as device_mobile_device_info, t.device.ip as device_ip, t.device.userAgent as device_user_agent, t.device.language as device_language,
  geoNetwork.country as geoNetwork_country, geoNetwork.countryIsoCode as geoNetwork_country_iso_code, geoNetwork.region as geoNetwork_region,
  geoNetwork.regionIsoCode as geoNetwork_region_iso_code, geoNetwork.city as geoNetwork_city,geoNetwork.latitude as geoNetwork_latitude, geoNetwork.longitude  as geoNetwork_longitude,
  trafficSource.channelGrouping as trafficSource_channelGrouping, trafficSource.source as trafficSource_source, trafficSource.medium as trafficSource_medium, 
  trafficSource.campaign as trafficSource_campaign, trafficSource.keyword as trafficSource_keyword, trafficSource.adContent as trafficSource_adContent, trafficSource.adGroup as trafficSource_adGroup,
  trafficSource.keywordMatchType as trafficSource_keywordMatchType, --trafficSource.adwordsClickInfo as trafficSource_adwordsClickInfo, --trafficSource.adMatchedQuery as trafficSource_adMatchedQuery,
  trafficSource.gclid as trafficSource_gclid, trafficSource.dclid as trafficSource_dclid, cast(trafficSource.isTrueDirect as smallint) as trafficSource_isTrueDirect, trafficSource.adCost as trafficSource_adCost,
  trafficSource.attributedAdCost as trafficSource_attributedAdCost,
  totals.hits as totals_hits, totals.events as totals_events, totals.transactions as totals_transactions, totals.pageviews as totals_pageviews, totals.screenviews as totals_screenviews, 
  CAST(totals.isInteraction as smallint) as totals_isInteraction, totals.visits as totals_visits,
  totalsStreaming.hits as totalsStreaming_hits, totalsStreaming.events as totalsStreaming_events, totalsStreaming.transactions as totalsStreaming_transactions, totalsStreaming.pageviews as totalsStreaming_pageviews,
  totalsStreaming.screenviews as totalsStreaming_screenviews, CAST(totalsStreaming.isInteraction as smallint) as totalsStreaming_isInteraction,
  json_format(CAST(t.hits as JSON)) as hits,json_format(CAST(t.customDimensions as JSON)) as customDimensions
from bigquery.owoxbi_streaming.owoxbi_sessions_20190228 as t