code: """
  # My first app
  Here's our first attempt at using data to create a table:
  """
  import logging
  import sys
  
  import streamlit as st
  from clickhouse_connect import common
  from llama_index.core.settings import Settings
  from llama_index.embeddings.fastembed import FastEmbedEmbedding
  from llama_index.llms.openai import OpenAI
  
  from llama_index.core import VectorStoreIndex, PromptTemplate
  from llama_index.core.indices.struct_store import NLSQLTableQueryEngine
  from llama_index.core.indices.vector_store import VectorIndexAutoRetriever
  from llama_index.core.indices.vector_store.retrievers.auto_retriever.prompts import PREFIX, EXAMPLES
  from llama_index.core.prompts import PromptType
  from llama_index.core.query_engine import RetrieverQueryEngine, SQLAutoVectorQueryEngine
  from llama_index.core.tools import QueryEngineTool
  from llama_index.core.vector_stores.types import VectorStoreInfo, MetadataInfo
  from llama_index.vector_stores.clickhouse import ClickHouseVectorStore
  import clickhouse_connect
  import openai
  from sqlalchemy import (
      create_engine,
  )
  from llama_index.core import SQLDatabase
  
  logging.basicConfig(stream=sys.stdout, level=logging.INFO)
  logging.getLogger().addHandler(logging.StreamHandler(stream=sys.stdout))
  
  host = st.secrets.clickhouse.host
  password = st.secrets.clickhouse.password
  username = st.secrets.clickhouse.username
  secure = st.secrets.clickhouse.secure
  http_port = st.secrets.clickhouse.http_port
  native_port = st.secrets.clickhouse.native_port
  open_ai_model = "gpt-4"
  
  database = st.secrets.clickhouse.database
  hackernews_table = st.secrets.clickhouse.hackernews_table
  stackoverflow_table = st.secrets.clickhouse.stackoverflow_table
  database = st.secrets.clickhouse.database
  
  st.set_page_config(
      page_title="Get summaries of Hacker News posts enriched with Stackoverflow survey results, powered by LlamaIndex and ClickHouse",
      page_icon="🦙🚀", layout="centered", initial_sidebar_state="auto", menu_items=None)
  st.title("💬HackBot powered by LlamaIndex 🦙 and ClickHouse 🚀")
  st.info(
      "Check out the full [blog post](https://clickhouse.com/blog/building-a-hackernews-chat-bot-with-llama-index-with-clickhouse/) for this app",
      icon="📃")
  st.caption("A Streamlit chatbot 💬 for Hacker News powered by LlamaIndex 🦙 and ClickHouse 🚀")
  
  
  @st.cache_resource
  def load_embedding():
      return FastEmbedEmbedding(
          model_name="sentence-transformers/all-MiniLM-L6-v2",
          max_length=384,
          cache_dir="./embeddings/"
      )
  
  
  Settings.embed_model = load_embedding()
  
  CLICKHOUSE_TEXT_TO_SQL_TMPL = (
      "Given an input question, first create a syntactically correct ClickHouse SQL "
      "query to run, then look at the results of the query and return the answer. "
      "You can order the results by a relevant column to return the most "
      "interesting examples in the database.\n\n"
      "Never query for all the columns from a specific table, only ask for a "
      "few relevant columns given the question.\n\n"
      "Pay attention to use only the column names that you can see in the schema "
      "description. "
      "Be careful to not query for columns that do not exist. "
      "Pay attention to which column is in which table. "
      "Also, qualify column names with the table name when needed. \n"
      "If needing to group on Array Columns use the ClickHouse function arrayJoin e.g. arrayJoin(columnName) \n"
      "For example, the following query identifies the most popular database:\n"
      "SELECT d, count(*) AS count FROM so_surveys GROUP BY "
      "arrayJoin(database_want_to_work_with) AS d ORDER BY count DESC LIMIT 1\n"
      "You are required to use the following format, each taking one line:\n\n"
      "Question: Question here\n"
      "SQLQuery: SQL Query to run\n"
      "SQLResult: Result of the SQLQuery\n"
      "Answer: Final answer here\n\n"
      "Only use tables listed below.\n"
      "{schema}\n\n"
      "Question: {query_str}\n"
      "SQLQuery: "
  )
  
  CLICKHOUSE_TEXT_TO_SQL_PROMPT = PromptTemplate(
      CLICKHOUSE_TEXT_TO_SQL_TMPL,
      prompt_type=PromptType.TEXT_TO_SQL,
  )
  
  CLICKHOUSE_CUSTOM_SUFFIX = """
  The following is the datasource schema to work with. 
  IMPORTANT: Make sure that filters are only used as needed and only suggest filters for fields in the data source.
  
  Data Source:
  ```json
  {info_str}
  ```
  
  User Query:
  {query_str}
  
  Structured Request:
  """
  
  CLICKHOUSE_VECTOR_STORE_QUERY_PROMPT_TMPL = PREFIX + EXAMPLES + CLICKHOUSE_CUSTOM_SUFFIX
  
  
  @st.cache_resource
  def clickhouse():
      common.set_setting('autogenerate_session_id', False)
      return clickhouse_connect.get_client(
          host=host, port=http_port, username=username, password=password,
          secure=secure, settings={"max_parallel_replicas": "3", "use_hedged_requests": "0",
                                   "allow_experimental_parallel_reading_from_replicas": "1"}
      )
  
  
  def sql_auto_vector_query_engine():
      with st.spinner(text="Preparing indexes. This should take a few seconds. No time to make 🫖"):
          engine = create_engine(
              f'clickhouse+native://{username}:{password}@{host}:' +
              f'{native_port}/{database}?compression=lz4&secure={secure}'
          )
          sql_database = SQLDatabase(engine, include_tables=[stackoverflow_table], view_support=True)
          vector_store = ClickHouseVectorStore(clickhouse_client=clickhouse(), table=hackernews_table)
          vector_index = VectorStoreIndex.from_vector_store(vector_store)
          return sql_database, vector_index
  
  
  def get_engine(min_length, score, min_date):
      sql_database, vector_index = sql_auto_vector_query_engine()
  
      nl_sql_engine = NLSQLTableQueryEngine(
          sql_database=sql_database,
          tables=[stackoverflow_table],
          text_to_sql_prompt=CLICKHOUSE_TEXT_TO_SQL_PROMPT,
          llm=OpenAI(model=open_ai_model)
      )
      vector_store_info = VectorStoreInfo(
          content_info="Social news posts and comments from users",
          metadata_info=[
              MetadataInfo(
                  name="post_score", type="int", description="Score of the comment or post",
              ),
              MetadataInfo(
                  name="by", type="str", description="the author or person who posted the comment",
              ),
              MetadataInfo(
                  name="time", type="date", description="the time at which the post or comment was made",
              ),
          ]
      )
  
      vector_auto_retriever = VectorIndexAutoRetriever(
          vector_index, vector_store_info=vector_store_info, similarity_top_k=10,
          prompt_template_str=CLICKHOUSE_VECTOR_STORE_QUERY_PROMPT_TMPL, llm=OpenAI(model=open_ai_model),
          vector_store_kwargs={"where": f"length >= {min_length} AND post_score >= {score} AND time >= '{min_date}'"}
      )
  
      retriever_query_engine = RetrieverQueryEngine.from_args(vector_auto_retriever, llm=OpenAI(model=open_ai_model))
  
      sql_tool = QueryEngineTool.from_defaults(
          query_engine=nl_sql_engine,
          description=(
              "Useful for translating a natural language query into a SQL query over"
              f" a table: {stackoverflow_table}, containing the survey responses on"
              f" different types of technology users currently use and want to use"
          ),
      )
      vector_tool = QueryEngineTool.from_defaults(
          query_engine=retriever_query_engine,
          description=(
              f"Useful for answering semantic questions abouts users comments and posts"
          ),
      )
  
      return SQLAutoVectorQueryEngine(
          sql_tool, vector_tool, llm=OpenAI(model=open_ai_model)
      )
  
  
  # identify the value ranges for our score, length and date widgets
  if "max_score" not in st.session_state.keys():
      client = clickhouse()
      st.session_state.max_score = int(
          client.query("SELECT max(post_score) FROM default.hackernews_llama").first_row[0])
      st.session_state.max_length = int(
          client.query("SELECT max(length) FROM default.hackernews_llama").first_row[0])
      st.session_state.min_date, st.session_state.max_date = client.query(
          "SELECT min(toDate(time)), max(toDate(time)) FROM default.hackernews_llama WHERE time != '1970-01-01 00:00:00'").first_row
  
  # set the initial message on load. Store in the session.
  if "messages" not in st.session_state:
      st.session_state.messages = [
          {"role": "assistant", "content": "Ask me a question about opinions on Hacker News and Stackoverflow!"}]
  
  # build the sidebar with our filters
  with st.sidebar:
      score = st.slider('Min Score', 0, st.session_state.max_score, value=0)
      min_length = st.slider('Min comment Length (tokens)', 0, st.session_state.max_length, value=20)
      min_date = st.date_input('Min comment date', value=st.session_state.min_date, min_value=st.session_state.min_date,
                               max_value=st.session_state.max_date)
      openai_api_key = st.text_input("Open API Key", key="chatbot_api_key", type="password")
      openai.api_key = openai_api_key
      "[Get an OpenAI API key](https://platform.openai.com/account/api-keys)"
      "[View the source code](https://github.com/ClickHouse/examples/blob/main/blog-examples/llama-index/hacknernews_app/hacker_insights.py)"
  # grab the users OPENAI api key. Don’t allow questions if not entered.
  if not openai_api_key:
      st.info("Please add your OpenAI API key to continue.")
      st.stop()
  
  if prompt := st.chat_input(placeholder="Your question about Hacker News"):
      st.session_state.messages.append({"role": "user", "content": prompt})
  # Display the prior chat messages
  for message in st.session_state.messages:  # Display the prior chat messages
      with st.chat_message(message["role"]):
          st.write(message["content"])
  
  # If last message is not from assistant, generate a new response
  if st.session_state.messages[-1]["role"] != "assistant":
      with st.chat_message("assistant"):
          with st.spinner("Thinking..."):
              # Query our engine for the answer and write to the page
              response = str(get_engine(min_length, score, min_date).query(prompt))
              st.write(response)
              st.session_state.messages.append({"role": "assistant", "content": response})
  
apis: llama_index.core.PromptTemplate llama_index.core.vector_stores.types.MetadataInfo llama_index.core.VectorStoreIndex.from_vector_store llama_index.llms.openai.OpenAI llama_index.core.tools.QueryEngineTool.from_defaults llama_index.core.SQLDatabase llama_index.embeddings.fastembed.FastEmbedEmbedding
extract_api: [((1100, 1158), 'logging.basicConfig', 'logging.basicConfig', ([], {'stream': 'sys.stdout', 'level': 'logging.INFO'}), '(stream=sys.stdout, level=logging.INFO)\n', (1119, 1158), False, 'import logging\n'), ((1713, 1957), 'streamlit.set_page_config', 'st.set_page_config', ([], {'page_title': '"""Get summaries of Hacker News posts enriched with Stackoverflow survey results, powered by LlamaIndex and ClickHouse"""', 'page_icon': '"""🦙🚀"""', 'layout': '"""centered"""', 'initial_sidebar_state': '"""auto"""', 'menu_items': 'None'}), "(page_title=\n    'Get summaries of Hacker News posts enriched with Stackoverflow survey results, powered by LlamaIndex and ClickHouse'\n    , page_icon='🦙🚀', layout='centered', initial_sidebar_state='auto',\n    menu_items=None)\n", (1731, 1957), True, 'import streamlit as st\n'), ((1953, 2014), 'streamlit.title', 'st.title', (['"""💬HackBot powered by LlamaIndex 🦙 and ClickHouse 🚀"""'], {}), "('💬HackBot powered by LlamaIndex 🦙 and ClickHouse 🚀')\n", (1961, 2014), True, 'import streamlit as st\n'), ((2015, 2183), 'streamlit.info', 'st.info', (['"""Check out the full [blog post](https://clickhouse.com/blog/building-a-hackernews-chat-bot-with-llama-index-with-clickhouse/) for this app"""'], {'icon': '"""📃"""'}), "(\n    'Check out the full [blog post](https://clickhouse.com/blog/building-a-hackernews-chat-bot-with-llama-index-with-clickhouse/) for this app'\n    , icon='📃')\n", (2022, 2183), True, 'import streamlit as st\n'), ((2183, 2285), 'streamlit.caption', 'st.caption', (['"""A Streamlit chatbot 💬 for Hacker News powered by LlamaIndex 🦙 and ClickHouse 🚀"""'], {}), "(\n    'A Streamlit chatbot 💬 for Hacker News powered by LlamaIndex 🦙 and ClickHouse 🚀'\n    )\n", (2193, 2285), True, 'import streamlit as st\n'), ((3944, 4023), 'llama_index.core.PromptTemplate', 'PromptTemplate', (['CLICKHOUSE_TEXT_TO_SQL_TMPL'], {'prompt_type': 'PromptType.TEXT_TO_SQL'}), '(CLICKHOUSE_TEXT_TO_SQL_TMPL, prompt_type=PromptType.TEXT_TO_SQL)\n', (3958, 4023), False, 'from llama_index.core import VectorStoreIndex, PromptTemplate\n'), ((1190, 1230), 'logging.StreamHandler', 'logging.StreamHandler', ([], {'stream': 'sys.stdout'}), '(stream=sys.stdout)\n', (1211, 1230), False, 'import logging\n'), ((2330, 2448), 'llama_index.embeddings.fastembed.FastEmbedEmbedding', 'FastEmbedEmbedding', ([], {'model_name': '"""sentence-transformers/all-MiniLM-L6-v2"""', 'max_length': '(384)', 'cache_dir': '"""./embeddings/"""'}), "(model_name='sentence-transformers/all-MiniLM-L6-v2',\n    max_length=384, cache_dir='./embeddings/')\n", (2348, 2448), False, 'from llama_index.embeddings.fastembed import FastEmbedEmbedding\n'), ((4455, 4507), 'clickhouse_connect.common.set_setting', 'common.set_setting', (['"""autogenerate_session_id"""', '(False)'], {}), "('autogenerate_session_id', False)\n", (4473, 4507), False, 'from clickhouse_connect import common\n'), ((4519, 4767), 'clickhouse_connect.get_client', 'clickhouse_connect.get_client', ([], {'host': 'host', 'port': 'http_port', 'username': 'username', 'password': 'password', 'secure': 'secure', 'settings': "{'max_parallel_replicas': '3', 'use_hedged_requests': '0',\n    'allow_experimental_parallel_reading_from_replicas': '1'}"}), "(host=host, port=http_port, username=username,\n    password=password, secure=secure, settings={'max_parallel_replicas':\n    '3', 'use_hedged_requests': '0',\n    'allow_experimental_parallel_reading_from_replicas': '1'})\n", (4548, 4767), False, 'import clickhouse_connect\n'), ((6832, 7118), 'llama_index.core.tools.QueryEngineTool.from_defaults', 'QueryEngineTool.from_defaults', ([], {'query_engine': 'nl_sql_engine', 'description': 'f"""Useful for translating a natural language query into a SQL query over a table: {stackoverflow_table}, containing the survey responses on different types of technology users currently use and want to use"""'}), "(query_engine=nl_sql_engine, description=\n    f'Useful for translating a natural language query into a SQL query over a table: {stackoverflow_table}, containing the survey responses on different types of technology users currently use and want to use'\n    )\n", (6861, 7118), False, 'from llama_index.core.tools import QueryEngineTool\n'), ((7205, 7368), 'llama_index.core.tools.QueryEngineTool.from_defaults', 'QueryEngineTool.from_defaults', ([], {'query_engine': 'retriever_query_engine', 'description': 'f"""Useful for answering semantic questions abouts users comments and posts"""'}), "(query_engine=retriever_query_engine,\n    description=\n    f'Useful for answering semantic questions abouts users comments and posts')\n", (7234, 7368), False, 'from llama_index.core.tools import QueryEngineTool\n'), ((7605, 7628), 'streamlit.session_state.keys', 'st.session_state.keys', ([], {}), '()\n', (7626, 7628), True, 'import streamlit as st\n'), ((8425, 8487), 'streamlit.slider', 'st.slider', (['"""Min Score"""', '(0)', 'st.session_state.max_score'], {'value': '(0)'}), "('Min Score', 0, st.session_state.max_score, value=0)\n", (8434, 8487), True, 'import streamlit as st\n'), ((8505, 8591), 'streamlit.slider', 'st.slider', (['"""Min comment Length (tokens)"""', '(0)', 'st.session_state.max_length'], {'value': '(20)'}), "('Min comment Length (tokens)', 0, st.session_state.max_length,\n    value=20)\n", (8514, 8591), True, 'import streamlit as st\n'), ((8603, 8747), 'streamlit.date_input', 'st.date_input', (['"""Min comment date"""'], {'value': 'st.session_state.min_date', 'min_value': 'st.session_state.min_date', 'max_value': 'st.session_state.max_date'}), "('Min comment date', value=st.session_state.min_date,\n    min_value=st.session_state.min_date, max_value=st.session_state.max_date)\n", (8616, 8747), True, 'import streamlit as st\n'), ((8794, 8863), 'streamlit.text_input', 'st.text_input', (['"""Open API Key"""'], {'key': '"""chatbot_api_key"""', 'type': '"""password"""'}), "('Open API Key', key='chatbot_api_key', type='password')\n", (8807, 8863), True, 'import streamlit as st\n'), ((9214, 9268), 'streamlit.info', 'st.info', (['"""Please add your OpenAI API key to continue."""'], {}), "('Please add your OpenAI API key to continue.')\n", (9221, 9268), True, 'import streamlit as st\n'), ((9273, 9282), 'streamlit.stop', 'st.stop', ([], {}), '()\n', (9280, 9282), True, 'import streamlit as st\n'), ((9297, 9357), 'streamlit.chat_input', 'st.chat_input', ([], {'placeholder': '"""Your question about Hacker News"""'}), "(placeholder='Your question about Hacker News')\n", (9310, 9357), True, 'import streamlit as st\n'), ((9363, 9432), 'streamlit.session_state.messages.append', 'st.session_state.messages.append', (["{'role': 'user', 'content': prompt}"], {}), "({'role': 'user', 'content': prompt})\n", (9395, 9432), True, 'import streamlit as st\n'), ((1159, 1178), 'logging.getLogger', 'logging.getLogger', ([], {}), '()\n', (1176, 1178), False, 'import logging\n'), ((4858, 4950), 'streamlit.spinner', 'st.spinner', ([], {'text': '"""Preparing indexes. This should take a few seconds. No time to make 🫖"""'}), "(text=\n    'Preparing indexes. This should take a few seconds. No time to make 🫖')\n", (4868, 4950), True, 'import streamlit as st\n'), ((4964, 5097), 'sqlalchemy.create_engine', 'create_engine', (["(f'clickhouse+native://{username}:{password}@{host}:' +\n    f'{native_port}/{database}?compression=lz4&secure={secure}')"], {}), "(f'clickhouse+native://{username}:{password}@{host}:' +\n    f'{native_port}/{database}?compression=lz4&secure={secure}')\n", (4977, 5097), False, 'from sqlalchemy import create_engine\n'), ((5151, 5227), 'llama_index.core.SQLDatabase', 'SQLDatabase', (['engine'], {'include_tables': '[stackoverflow_table]', 'view_support': '(True)'}), '(engine, include_tables=[stackoverflow_table], view_support=True)\n', (5162, 5227), False, 'from llama_index.core import SQLDatabase\n'), ((5352, 5400), 'llama_index.core.VectorStoreIndex.from_vector_store', 'VectorStoreIndex.from_vector_store', (['vector_store'], {}), '(vector_store)\n', (5386, 5400), False, 'from llama_index.core import VectorStoreIndex, PromptTemplate\n'), ((9553, 9585), 'streamlit.chat_message', 'st.chat_message', (["message['role']"], {}), "(message['role'])\n", (9568, 9585), True, 'import streamlit as st\n'), ((9595, 9623), 'streamlit.write', 'st.write', (["message['content']"], {}), "(message['content'])\n", (9603, 9623), True, 'import streamlit as st\n'), ((9756, 9784), 'streamlit.chat_message', 'st.chat_message', (['"""assistant"""'], {}), "('assistant')\n", (9771, 9784), True, 'import streamlit as st\n'), ((5741, 5768), 'llama_index.llms.openai.OpenAI', 'OpenAI', ([], {'model': 'open_ai_model'}), '(model=open_ai_model)\n', (5747, 5768), False, 'from llama_index.llms.openai import OpenAI\n'), ((6548, 6575), 'llama_index.llms.openai.OpenAI', 'OpenAI', ([], {'model': 'open_ai_model'}), '(model=open_ai_model)\n', (6554, 6575), False, 'from llama_index.llms.openai import OpenAI\n'), ((6787, 6814), 'llama_index.llms.openai.OpenAI', 'OpenAI', ([], {'model': 'open_ai_model'}), '(model=open_ai_model)\n', (6793, 6814), False, 'from llama_index.llms.openai import OpenAI\n'), ((7480, 7507), 'llama_index.llms.openai.OpenAI', 'OpenAI', ([], {'model': 'open_ai_model'}), '(model=open_ai_model)\n', (7486, 7507), False, 'from llama_index.llms.openai import OpenAI\n'), ((9799, 9824), 'streamlit.spinner', 'st.spinner', (['"""Thinking..."""'], {}), "('Thinking...')\n", (9809, 9824), True, 'import streamlit as st\n'), ((9988, 10006), 'streamlit.write', 'st.write', (['response'], {}), '(response)\n', (9996, 10006), True, 'import streamlit as st\n'), ((10019, 10095), 'streamlit.session_state.messages.append', 'st.session_state.messages.append', (["{'role': 'assistant', 'content': response}"], {}), "({'role': 'assistant', 'content': response})\n", (10051, 10095), True, 'import streamlit as st\n'), ((5918, 6010), 'llama_index.core.vector_stores.types.MetadataInfo', 'MetadataInfo', ([], {'name': '"""post_score"""', 'type': '"""int"""', 'description': '"""Score of the comment or post"""'}), "(name='post_score', type='int', description=\n    'Score of the comment or post')\n", (5930, 6010), False, 'from llama_index.core.vector_stores.types import VectorStoreInfo, MetadataInfo\n'), ((6050, 6149), 'llama_index.core.vector_stores.types.MetadataInfo', 'MetadataInfo', ([], {'name': '"""by"""', 'type': '"""str"""', 'description': '"""the author or person who posted the comment"""'}), "(name='by', type='str', description=\n    'the author or person who posted the comment')\n", (6062, 6149), False, 'from llama_index.core.vector_stores.types import VectorStoreInfo, MetadataInfo\n'), ((6189, 6294), 'llama_index.core.vector_stores.types.MetadataInfo', 'MetadataInfo', ([], {'name': '"""time"""', 'type': '"""date"""', 'description': '"""the time at which the post or comment was made"""'}), "(name='time', type='date', description=\n    'the time at which the post or comment was made')\n", (6201, 6294), False, 'from llama_index.core.vector_stores.types import VectorStoreInfo, MetadataInfo\n')]