CREATE TABLE default.ga_daily
(
        `event_date` Date,
        `event_timestamp` DateTime64(3),
        `event_name` String,
        `event_params` Map(String, String),
        `ga_session_number` MATERIALIZED CAST(event_params['ga_session_number'], 'Int64'),
        `ga_session_id` MATERIALIZED CAST(event_params['ga_session_id'], 'String'),
        `page_location` MATERIALIZED CAST(event_params['page_location'], 'String'),
        `page_title` MATERIALIZED CAST(event_params['page_title'], 'String'),
        `page_referrer`  MATERIALIZED CAST(event_params['page_referrer'], 'String'),
        `event_previous_timestamp` DateTime64(3),
        `event_bundle_sequence_id` Nullable(Int64),
        `event_server_timestamp_offset` Nullable(Int64),
        `user_id` Nullable(String),
        `user_pseudo_id` Nullable(String),
        `privacy_info` Tuple(analytics_storage Nullable(String), ads_storage Nullable(String), uses_transient_token Nullable(String)),
        `user_first_touch_timestamp` DateTime64(3),
        `device` Tuple(category Nullable(String), mobile_brand_name Nullable(String), mobile_model_name Nullable(String), mobile_marketing_name Nullable(String), mobile_os_hardware_model Nullable(String), operating_system Nullable(String), operating_system_version Nullable(String), vendor_id Nullable(String), advertising_id Nullable(String), language Nullable(String), is_limited_ad_tracking Nullable(String), time_zone_offset_seconds Nullable(Int64), browser Nullable(String), browser_version Nullable(String), web_info Tuple(browser Nullable(String), browser_version Nullable(String), hostname Nullable(String))),
        `geo` Tuple(city Nullable(String), country Nullable(String), continent Nullable(String), region Nullable(String), sub_continent Nullable(String), metro Nullable(String)),
        `app_info` Tuple(id Nullable(String), version Nullable(String), install_store Nullable(String), firebase_app_id Nullable(String), install_source Nullable(String)),
        `traffic_source` Tuple(name Nullable(String), medium Nullable(String), source Nullable(String)),    
        `stream_id` Nullable(String),
        `platform` Nullable(String),
        `event_dimensions` Tuple(hostname Nullable(String)),
        `collected_traffic_source` Tuple(manual_campaign_id Nullable(String), manual_campaign_name Nullable(String), manual_source Nullable(String), manual_medium Nullable(String), manual_term Nullable(String), manual_content Nullable(String), gclid Nullable(String), dclid Nullable(String), srsltid Nullable(String)),
        `is_active_user` Nullable(Bool)
)
ENGINE = MergeTree
ORDER BY (event_timestamp, event_name, ga_session_id)

#!/usr/bin/python3
import json
import sys
from bedrock import get_bedrock_client
from tenacity import (
   retry,
   stop_after_attempt,
   wait_random_exponential,
)
import logging
logging.basicConfig(filename='embed.log', level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
           
bedrock_runtime = get_bedrock_client(region="us-east-1", silent=True)
           
accept = "application/json"
contentType = "application/json"
modelId = "amazon.titan-embed-text-v1"
char_limit = 10000  # 1500 tokens effectively
           
           
@retry(wait=wait_random_exponential(min=1, max=60), stop=stop_after_attempt(20))
def embeddings_with_backoff(**kwargs):
   return bedrock_runtime.invoke_model(**kwargs)
           
           
for size in sys.stdin:
   # collect batch to process
   for row in range(0, int(size)):
       try:
           text = sys.stdin.readline()
           text = text[:char_limit]
           body = json.dumps({"inputText": text})
           response = embeddings_with_backoff(
               body=body, modelId=modelId, accept=accept, contentType=contentType
           )
           response_body = json.loads(response.get("body").read())
           embedding = response_body.get("embedding")
           print(json.dumps(embedding))
       except Exception as e:
           logging.error(e)
           print(json.dumps([]))    
   sys.stdout.flush()

 
                 embed
                  executable_pool
                  3
                  true
                  TabSeparated
                  Array(Float32)
                  
                    String
        
                  embed.py
                  10000000
                  10000000
                  1000000
   

SELECT embed('some example text')
FORMAT Vertical
           
Row 1:
──────
embed('some example text'): [0.5078125,0.09472656,..,0.18652344]
           
1 row in set. Elapsed: 0.444 sec.

INSERT INTO pages SELECT url, title, content, embed(content) as embedding FROM site_pages_raw SETTINGS  merge_tree_min_rows_for_concurrent_read = 1, merge_tree_min_bytes_for_concurrent_read=0, min_insert_block_size_rows=10, min_insert_block_size_bytes=0

CREATE TABLE source_pages
(
        `url` String,    
        `raw_title` String,
        `raw_content` String
)
ENGINE = MergeTree
ORDER BY url
           
CREATE TABLE site_pages
(
        `url` String,
        `raw_title` String,
        `raw_content` String,
        `title` String,
        `content` String,
        `embedding` Array(Float32)
)
ENGINE = MergeTree
ORDER BY url
           
INSERT INTO site_pages
SELECT url, raw_title, raw_content, extractTextFromHTML(raw_title) AS title, extractTextFromHTML(raw_content) AS content, embed(arrayStringConcat(arraySlice(tokens(concat(title, content)), 1, 300), ' ')) AS embedding
FROM source_pages
SETTINGS merge_tree_min_rows_for_concurrent_read = 1, merge_tree_min_bytes_for_concurrent_read = 0, min_insert_block_size_rows = 10, min_insert_block_size_bytes = 0
           
0 rows in set. Elapsed: 390.835 sec. Processed 2.95 thousand rows, 113.37 MB (7.55 rows/s., 290.08 KB/s.)
Peak memory usage: 266.70 MiB.

/* Answer the following: To filter by pages containing words: */
 SELECT page_location FROM ga_daily WHERE page_location IN (SELECT url FROM site_pages WHERE content ILIKE 'dictionary engine' OR content ILIKE 'dictionary functions' OR content ILIKE 'dictionary' OR content ILIKE 'dictionary configuration' OR content ILIKE 'cache' OR content ILIKE 'dictionary updates' OR content ILIKE 'dictionaries')

What are the number of new users for blogs about dictionaries over time? 
           
becomes…
           
What are the number of new users for blogs about dictionaries over time? For the topic of dictionaries, filter by dictionary configuration,dictionaries,dictionary functions,cache,dictionary engine,dictionary,dictionary updates

SELECT event_date, uniqExact(user_pseudo_id) AS returning_users
FROM ga_daily
WHERE (event_name = 'session_start') AND is_active_user AND (ga_session_number > 1 OR user_first_touch_timestamp < event_date)
GROUP BY event_date
ORDER BY event_date ASC

/*Answer the following: find returning users:*/
SELECT uniqExact(user_pseudo_id) AS returning_users FROM ga_daily WHERE (event_name = 'session_start') AND is_active_user AND (ga_session_number > 1 OR user_first_touch_timestamp < event_date)

CREATE TABLE default.questions
(    
  `question` String,
  `query` String,
  `embedding` Array(Float32)
)
ENGINE = MergeTree
ORDER BY question

SELECT question, query FROM questions ORDER BY cosineDistance(embedding, embed("Show me returning users over the last 30 days for blog posts")) ASC LIMIT 3

SELECT question, query FROM questions ORDER BY cosineDistance(embedding, embed("returning users")) ASC LIMIT 1

Human: You have to generate ClickHouse SQL using natural language query/request. Your goal -- create accurate ClickHouse SQL statements and help the user extract data from ClickHouse database. You will be provided with rules, database schemaand relevant SQL statement examples .
           
This is the table schema for ga_daily.
           
          
CREATE TABLE ga_daily
(
        `event_date` Date,
        `event_timestamp` DateTime64(3),
        `event_name` Nullable(String),
        `event_params` Map(String, String),
        `ga_session_number` MATERIALIZED CAST(event_params['ga_session_number'], 'Int64'),    
        `ga_session_id` MATERIALIZED CAST(event_params['ga_session_id'], 'String'),
        `page_location` MATERIALIZED CAST(event_params['page_location'], 'String'),
        `page_title` MATERIALIZED CAST(event_params['page_title'], 'String'),
        `page_referrer`  MATERIALIZED CAST(event_params['page_referrer'], 'String'),
        `event_previous_timestamp` DateTime64(3),
        `event_bundle_sequence_id` Nullable(Int64),
        `event_server_timestamp_offset` Nullable(Int64),
        `user_id` Nullable(String),
        `user_pseudo_id` Nullable(String),
        `privacy_info` Tuple(analytics_storage Nullable(String), ads_storage Nullable(String), uses_transient_token Nullable(String)),
        `user_first_touch_timestamp` DateTime64(3),
        `device` Tuple(category Nullable(String), mobile_brand_name Nullable(String), mobile_model_name Nullable(String), mobile_marketing_name Nullable(String), mobile_os_hardware_model Nullable(String), operating_system Nullable(String), operating_system_version Nullable(String), vendor_id Nullable(String), advertising_id Nullable(String), language Nullable(String), is_limited_ad_tracking Nullable(String), time_zone_offset_seconds Nullable(Int64), browser Nullable(String), browser_version Nullable(String), web_info Tuple(browser Nullable(String), browser_version Nullable(String), hostname Nullable(String))),
        `geo` Tuple(city Nullable(String), country Nullable(String), continent Nullable(String), region Nullable(String), sub_continent Nullable(String), metro Nullable(String)),
        `app_info` Tuple(id Nullable(String), version Nullable(String), install_store Nullable(String), firebase_app_id Nullable(String), install_source Nullable(String)),
        `traffic_source` Tuple(name Nullable(String), medium Nullable(String), source Nullable(String)),
        `stream_id` Nullable(String),
        `platform` Nullable(String),
        `event_dimensions` Tuple(hostname Nullable(String)),
        `collected_traffic_source` Tuple(manual_campaign_id Nullable(String), manual_campaign_name Nullable(String), manual_source Nullable(String), manual_medium Nullable(String), manual_term Nullable(String), manual_content Nullable(String), gclid Nullable(String), dclid Nullable(String), srsltid Nullable(String)),
        `is_active_user` Nullable(Bool)
)
ENGINE = MergeTree
ORDER BY event_timestamp


           
This is the table schema for site_pages.
           
              
CREATE TABLE site_pages
(
        `url` String,
        `title` String,
        `content` String
)
ENGINE = MergeTree
ORDER BY url


           
          
You can use the tables "ga_daily" and "site_pages".  
           
The table ga_daily contains website analytics data with a row for user events. The following columns are important:
        - event_name - A string column. Filter by 'first_visit' if identifying new users, 'session_start' for returning users and 'page_view' for page views.
        - event_date - A Date column on which the event occured
        - event_timestamp - A DateTime64(3) with the event time to milli-second accuracy
        - ga_session_id - A string identifying a user session.
        - ga_session_number - The session number for the user
        - user_pseudo_id - A string uniquely identifying a user
        - is_active_user - A boolean indicating if the user was active. True if active or engaged.
        - user_first_touch_timestamp - The first time a user visited the site.
        - page_location - the full url of the page.
        - page_title - The page title.
        - page_referer - The referer for the page. A full url.
        - traffic_source.name provides the source of the traffic.


           
          
/*Answer the following: find new users:*/
SELECT count() AS new_users FROM ga_daily WHERE event_name = 'first_visit'
           
/*Answer the following: filter by blogs:*/
SELECT page_location FROM ga_daily WHERE page_location LIKE '%/blog/%'
           
/* Answer the following: To filter by pages containing words: */
 SELECT page_location FROM ga_daily WHERE page_location IN (SELECT url FROM site_pages WHERE content ILIKE 'cache' OR content ILIKE 'dictionary' OR content ILIKE 'dictionaries' OR content ILIKE 'dictionary configuration' OR content ILIKE 'dictionary updates' OR content ILIKE 'dictionary functions' OR content ILIKE 'dictionary engine')    
           


           
          Considering all above generate a ClickHouse SQL statement for the following query:
          What are the number of new users for blogs about dictionaries over time?. For the topic of dictionaries, filter by cache,dictionary,dictionaries,dictionary configuration,dictionary updates,dictionary functions,dictionary engine
           
Put result statement intags: