API tools faq
paste
Advertisement
jroakes

Embedding Model

jroakes
Jun 29th, 2021
801
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 14.11 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE MODEL `bigquery_ml.title_test`
  2. OPTIONS(model_type='kmeans',
  3.         num_clusters = 5,
  4.         DISTANCE_TYPE = 'cosine',
  5.         kmeans_init_method = 'KMEANS++') AS
  6. (
  7.  
  8.     WITH
  9.     raw_ga_4 AS (
  10.  
  11.         SELECT
  12.         * except(row)
  13.         FROM (
  14.         SELECT
  15.             -- extracts date from source table
  16.             parse_date('%Y%m%d',regexp_extract(_table_suffix,'[0-9]+')) as table_date,
  17.             -- flag to indicate if source table is `events_intraday_`
  18.             case when _table_suffix like '%intraday%' then true else false end as is_intraday,
  19.             *,
  20.             row_number() over (partition by user_pseudo_id, event_name, event_timestamp order by event_timestamp) as row
  21.         FROM
  22.             `project.analytics_XXXXXXXXXXXXXX.events_*`
  23.            
  24.             )
  25.         WHERE
  26.         row = 1
  27.  
  28.         ),
  29.  
  30.     pageviews AS (
  31.         SELECT
  32.             parse_date("%Y%m%d", event_date) event_date,
  33.             event_timestamp,
  34.             user_pseudo_id,
  35.             user_first_touch_timestamp,
  36.             device.category as device_category,
  37.             device.language as device_language,
  38.             device.web_info.browser as device_browser,
  39.             geo.continent as geo_continent,
  40.             geo.country as geo_country,
  41.             max(if(params.key = 'ga_session_id', params.value.int_value, null)) ga_session_id,
  42.             max(if(params.key = 'ga_session_number', params.value.int_value, null)) ga_session_number,
  43.             cast(max(if(params.key = 'session_engaged', params.value.string_value, null)) as int64) session_engaged,
  44.             max(if(params.key = 'page_title', params.value.string_value, null)) page_title,
  45.             max(if(params.key = 'page_location', params.value.string_value, null)) page_location,
  46.             max(if(params.key = 'source', params.value.string_value, null)) utm_source,
  47.             max(if(params.key = 'medium', params.value.string_value, null)) utm_medium,
  48.             max(if(params.key = 'campaign', params.value.string_value, null)) utm_campaign,
  49.             max(if(params.key = 'page_referrer', params.value.string_value, null)) utm_referrer
  50.             FROM raw_ga_4,
  51.             UNNEST(event_params) AS params
  52.             WHERE event_name = 'page_view'
  53.  
  54.             GROUP BY event_date, event_timestamp, user_pseudo_id, user_first_touch_timestamp, device_category, device_language, device_browser, geo_continent, geo_country
  55.             ),
  56.     unique_titles AS (
  57.         SELECT
  58.             DISTINCT(page_title)
  59.         FROM
  60.             pageviews
  61.         LIMIT 200
  62.     ),
  63.  
  64.     embeddings AS (
  65.         SELECT
  66.         text,
  67.         encoder AS embedding
  68.         FROM ML.PREDICT(MODEL `project.bigquery_ml.embedding_model`,(SELECT REGEXP_REPLACE(page_title, '[^\\w\\s]+', '') AS text FROM unique_titles))
  69.         JOIN (SELECT page_title, REGEXP_REPLACE(page_title, '[^\\w\\s]+', '') AS text FROM unique_titles) USING (text)
  70.            
  71.     ),
  72.  
  73.     arrays AS (
  74.     SELECT
  75.         -- 256 dimension embeddings
  76.         embedding[OFFSET(0)] as embed0,
  77.         embedding[OFFSET(1)] as embed1,
  78.         embedding[OFFSET(2)] as embed2,
  79.         embedding[OFFSET(3)] as embed3,
  80.         embedding[OFFSET(4)] as embed4,
  81.         embedding[OFFSET(5)] as embed5,
  82.         embedding[OFFSET(6)] as embed6,
  83.         embedding[OFFSET(7)] as embed7,
  84.         embedding[OFFSET(8)] as embed8,
  85.         embedding[OFFSET(9)] as embed9,
  86.         embedding[OFFSET(10)] as embed10,
  87.         embedding[OFFSET(11)] as embed11,
  88.         embedding[OFFSET(12)] as embed12,
  89.         embedding[OFFSET(13)] as embed13,
  90.         embedding[OFFSET(14)] as embed14,
  91.         embedding[OFFSET(15)] as embed15,
  92.         embedding[OFFSET(16)] as embed16,
  93.         embedding[OFFSET(17)] as embed17,
  94.         embedding[OFFSET(18)] as embed18,
  95.         embedding[OFFSET(19)] as embed19,
  96.         embedding[OFFSET(20)] as embed20,
  97.         embedding[OFFSET(21)] as embed21,
  98.         embedding[OFFSET(22)] as embed22,
  99.         embedding[OFFSET(23)] as embed23,
  100.         embedding[OFFSET(24)] as embed24,
  101.         embedding[OFFSET(25)] as embed25,
  102.         embedding[OFFSET(26)] as embed26,
  103.         embedding[OFFSET(27)] as embed27,
  104.         embedding[OFFSET(28)] as embed28,
  105.         embedding[OFFSET(29)] as embed29,
  106.         embedding[OFFSET(30)] as embed30,
  107.         embedding[OFFSET(31)] as embed31,
  108.         embedding[OFFSET(32)] as embed32,
  109.         embedding[OFFSET(33)] as embed33,
  110.         embedding[OFFSET(34)] as embed34,
  111.         embedding[OFFSET(35)] as embed35,
  112.         embedding[OFFSET(36)] as embed36,
  113.         embedding[OFFSET(37)] as embed37,
  114.         embedding[OFFSET(38)] as embed38,
  115.         embedding[OFFSET(39)] as embed39,
  116.         embedding[OFFSET(40)] as embed40,
  117.         embedding[OFFSET(41)] as embed41,
  118.         embedding[OFFSET(42)] as embed42,
  119.         embedding[OFFSET(43)] as embed43,
  120.         embedding[OFFSET(44)] as embed44,
  121.         embedding[OFFSET(45)] as embed45,
  122.         embedding[OFFSET(46)] as embed46,
  123.         embedding[OFFSET(47)] as embed47,
  124.         embedding[OFFSET(48)] as embed48,
  125.         embedding[OFFSET(49)] as embed49,
  126.         embedding[OFFSET(50)] as embed50,
  127.         embedding[OFFSET(51)] as embed51,
  128.         embedding[OFFSET(52)] as embed52,
  129.         embedding[OFFSET(53)] as embed53,
  130.         embedding[OFFSET(54)] as embed54,
  131.         embedding[OFFSET(55)] as embed55,
  132.         embedding[OFFSET(56)] as embed56,
  133.         embedding[OFFSET(57)] as embed57,
  134.         embedding[OFFSET(58)] as embed58,
  135.         embedding[OFFSET(59)] as embed59,
  136.         embedding[OFFSET(60)] as embed60,
  137.         embedding[OFFSET(61)] as embed61,
  138.         embedding[OFFSET(62)] as embed62,
  139.         embedding[OFFSET(63)] as embed63,
  140.         embedding[OFFSET(64)] as embed64,
  141.         embedding[OFFSET(65)] as embed65,
  142.         embedding[OFFSET(66)] as embed66,
  143.         embedding[OFFSET(67)] as embed67,
  144.         embedding[OFFSET(68)] as embed68,
  145.         embedding[OFFSET(69)] as embed69,
  146.         embedding[OFFSET(70)] as embed70,
  147.         embedding[OFFSET(71)] as embed71,
  148.         embedding[OFFSET(72)] as embed72,
  149.         embedding[OFFSET(73)] as embed73,
  150.         embedding[OFFSET(74)] as embed74,
  151.         embedding[OFFSET(75)] as embed75,
  152.         embedding[OFFSET(76)] as embed76,
  153.         embedding[OFFSET(77)] as embed77,
  154.         embedding[OFFSET(78)] as embed78,
  155.         embedding[OFFSET(79)] as embed79,
  156.         embedding[OFFSET(80)] as embed80,
  157.         embedding[OFFSET(81)] as embed81,
  158.         embedding[OFFSET(82)] as embed82,
  159.         embedding[OFFSET(83)] as embed83,
  160.         embedding[OFFSET(84)] as embed84,
  161.         embedding[OFFSET(85)] as embed85,
  162.         embedding[OFFSET(86)] as embed86,
  163.         embedding[OFFSET(87)] as embed87,
  164.         embedding[OFFSET(88)] as embed88,
  165.         embedding[OFFSET(89)] as embed89,
  166.         embedding[OFFSET(90)] as embed90,
  167.         embedding[OFFSET(91)] as embed91,
  168.         embedding[OFFSET(92)] as embed92,
  169.         embedding[OFFSET(93)] as embed93,
  170.         embedding[OFFSET(94)] as embed94,
  171.         embedding[OFFSET(95)] as embed95,
  172.         embedding[OFFSET(96)] as embed96,
  173.         embedding[OFFSET(97)] as embed97,
  174.         embedding[OFFSET(98)] as embed98,
  175.         embedding[OFFSET(99)] as embed99,
  176.         embedding[OFFSET(100)] as embed100,
  177.         embedding[OFFSET(101)] as embed101,
  178.         embedding[OFFSET(102)] as embed102,
  179.         embedding[OFFSET(103)] as embed103,
  180.         embedding[OFFSET(104)] as embed104,
  181.         embedding[OFFSET(105)] as embed105,
  182.         embedding[OFFSET(106)] as embed106,
  183.         embedding[OFFSET(107)] as embed107,
  184.         embedding[OFFSET(108)] as embed108,
  185.         embedding[OFFSET(109)] as embed109,
  186.         embedding[OFFSET(110)] as embed110,
  187.         embedding[OFFSET(111)] as embed111,
  188.         embedding[OFFSET(112)] as embed112,
  189.         embedding[OFFSET(113)] as embed113,
  190.         embedding[OFFSET(114)] as embed114,
  191.         embedding[OFFSET(115)] as embed115,
  192.         embedding[OFFSET(116)] as embed116,
  193.         embedding[OFFSET(117)] as embed117,
  194.         embedding[OFFSET(118)] as embed118,
  195.         embedding[OFFSET(119)] as embed119,
  196.         embedding[OFFSET(120)] as embed120,
  197.         embedding[OFFSET(121)] as embed121,
  198.         embedding[OFFSET(122)] as embed122,
  199.         embedding[OFFSET(123)] as embed123,
  200.         embedding[OFFSET(124)] as embed124,
  201.         embedding[OFFSET(125)] as embed125,
  202.         embedding[OFFSET(126)] as embed126,
  203.         embedding[OFFSET(127)] as embed127,
  204.         embedding[OFFSET(128)] as embed128,
  205.         embedding[OFFSET(129)] as embed129,
  206.         embedding[OFFSET(130)] as embed130,
  207.         embedding[OFFSET(131)] as embed131,
  208.         embedding[OFFSET(132)] as embed132,
  209.         embedding[OFFSET(133)] as embed133,
  210.         embedding[OFFSET(134)] as embed134,
  211.         embedding[OFFSET(135)] as embed135,
  212.         embedding[OFFSET(136)] as embed136,
  213.         embedding[OFFSET(137)] as embed137,
  214.         embedding[OFFSET(138)] as embed138,
  215.         embedding[OFFSET(139)] as embed139,
  216.         embedding[OFFSET(140)] as embed140,
  217.         embedding[OFFSET(141)] as embed141,
  218.         embedding[OFFSET(142)] as embed142,
  219.         embedding[OFFSET(143)] as embed143,
  220.         embedding[OFFSET(144)] as embed144,
  221.         embedding[OFFSET(145)] as embed145,
  222.         embedding[OFFSET(146)] as embed146,
  223.         embedding[OFFSET(147)] as embed147,
  224.         embedding[OFFSET(148)] as embed148,
  225.         embedding[OFFSET(149)] as embed149,
  226.         embedding[OFFSET(150)] as embed150,
  227.         embedding[OFFSET(151)] as embed151,
  228.         embedding[OFFSET(152)] as embed152,
  229.         embedding[OFFSET(153)] as embed153,
  230.         embedding[OFFSET(154)] as embed154,
  231.         embedding[OFFSET(155)] as embed155,
  232.         embedding[OFFSET(156)] as embed156,
  233.         embedding[OFFSET(157)] as embed157,
  234.         embedding[OFFSET(158)] as embed158,
  235.         embedding[OFFSET(159)] as embed159,
  236.         embedding[OFFSET(160)] as embed160,
  237.         embedding[OFFSET(161)] as embed161,
  238.         embedding[OFFSET(162)] as embed162,
  239.         embedding[OFFSET(163)] as embed163,
  240.         embedding[OFFSET(164)] as embed164,
  241.         embedding[OFFSET(165)] as embed165,
  242.         embedding[OFFSET(166)] as embed166,
  243.         embedding[OFFSET(167)] as embed167,
  244.         embedding[OFFSET(168)] as embed168,
  245.         embedding[OFFSET(169)] as embed169,
  246.         embedding[OFFSET(170)] as embed170,
  247.         embedding[OFFSET(171)] as embed171,
  248.         embedding[OFFSET(172)] as embed172,
  249.         embedding[OFFSET(173)] as embed173,
  250.         embedding[OFFSET(174)] as embed174,
  251.         embedding[OFFSET(175)] as embed175,
  252.         embedding[OFFSET(176)] as embed176,
  253.         embedding[OFFSET(177)] as embed177,
  254.         embedding[OFFSET(178)] as embed178,
  255.         embedding[OFFSET(179)] as embed179,
  256.         embedding[OFFSET(180)] as embed180,
  257.         embedding[OFFSET(181)] as embed181,
  258.         embedding[OFFSET(182)] as embed182,
  259.         embedding[OFFSET(183)] as embed183,
  260.         embedding[OFFSET(184)] as embed184,
  261.         embedding[OFFSET(185)] as embed185,
  262.         embedding[OFFSET(186)] as embed186,
  263.         embedding[OFFSET(187)] as embed187,
  264.         embedding[OFFSET(188)] as embed188,
  265.         embedding[OFFSET(189)] as embed189,
  266.         embedding[OFFSET(190)] as embed190,
  267.         embedding[OFFSET(191)] as embed191,
  268.         embedding[OFFSET(192)] as embed192,
  269.         embedding[OFFSET(193)] as embed193,
  270.         embedding[OFFSET(194)] as embed194,
  271.         embedding[OFFSET(195)] as embed195,
  272.         embedding[OFFSET(196)] as embed196,
  273.         embedding[OFFSET(197)] as embed197,
  274.         embedding[OFFSET(198)] as embed198,
  275.         embedding[OFFSET(199)] as embed199,
  276.         embedding[OFFSET(200)] as embed200,
  277.         embedding[OFFSET(201)] as embed201,
  278.         embedding[OFFSET(202)] as embed202,
  279.         embedding[OFFSET(203)] as embed203,
  280.         embedding[OFFSET(204)] as embed204,
  281.         embedding[OFFSET(205)] as embed205,
  282.         embedding[OFFSET(206)] as embed206,
  283.         embedding[OFFSET(207)] as embed207,
  284.         embedding[OFFSET(208)] as embed208,
  285.         embedding[OFFSET(209)] as embed209,
  286.         embedding[OFFSET(210)] as embed210,
  287.         embedding[OFFSET(211)] as embed211,
  288.         embedding[OFFSET(212)] as embed212,
  289.         embedding[OFFSET(213)] as embed213,
  290.         embedding[OFFSET(214)] as embed214,
  291.         embedding[OFFSET(215)] as embed215,
  292.         embedding[OFFSET(216)] as embed216,
  293.         embedding[OFFSET(217)] as embed217,
  294.         embedding[OFFSET(218)] as embed218,
  295.         embedding[OFFSET(219)] as embed219,
  296.         embedding[OFFSET(220)] as embed220,
  297.         embedding[OFFSET(221)] as embed221,
  298.         embedding[OFFSET(222)] as embed222,
  299.         embedding[OFFSET(223)] as embed223,
  300.         embedding[OFFSET(224)] as embed224,
  301.         embedding[OFFSET(225)] as embed225,
  302.         embedding[OFFSET(226)] as embed226,
  303.         embedding[OFFSET(227)] as embed227,
  304.         embedding[OFFSET(228)] as embed228,
  305.         embedding[OFFSET(229)] as embed229,
  306.         embedding[OFFSET(230)] as embed230,
  307.         embedding[OFFSET(231)] as embed231,
  308.         embedding[OFFSET(232)] as embed232,
  309.         embedding[OFFSET(233)] as embed233,
  310.         embedding[OFFSET(234)] as embed234,
  311.         embedding[OFFSET(235)] as embed235,
  312.         embedding[OFFSET(236)] as embed236,
  313.         embedding[OFFSET(237)] as embed237,
  314.         embedding[OFFSET(238)] as embed238,
  315.         embedding[OFFSET(239)] as embed239,
  316.         embedding[OFFSET(240)] as embed240,
  317.         embedding[OFFSET(241)] as embed241,
  318.         embedding[OFFSET(242)] as embed242,
  319.         embedding[OFFSET(243)] as embed243,
  320.         embedding[OFFSET(244)] as embed244,
  321.         embedding[OFFSET(245)] as embed245,
  322.         embedding[OFFSET(246)] as embed246,
  323.         embedding[OFFSET(247)] as embed247,
  324.         embedding[OFFSET(248)] as embed248,
  325.         embedding[OFFSET(249)] as embed249,
  326.         embedding[OFFSET(250)] as embed250,
  327.         embedding[OFFSET(251)] as embed251,
  328.         embedding[OFFSET(252)] as embed252,
  329.         embedding[OFFSET(253)] as embed253,
  330.         embedding[OFFSET(254)] as embed254,
  331.         embedding[OFFSET(255)] as embed255
  332.     FROM embeddings
  333.  
  334.     )
  335.    
  336.     SELECT * FROM arrays
  337.  
  338. )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!