BigQueryを直接言葉で検索する

1.はじめに

• langchainのSQLDatabaseToolkitとsqlalchemy-bigqueryを連携させれば、BigQueryを直接言葉で検索できる！

2.経緯

• 以前作成した投稿で、BigQueryから抽出したデータを、いったんsqlite3に格納して、それをLLMで呼び出して言葉で検索できるようにする、というのを試した。

#@title bigqueryで取得
query_job = client.query(query2)
rows = query_job.result()
df = rows.to_dataframe()

#@title sqliteに格納
file_sqlite3 = "./patentdata.db"
conn = sqlite3.connect(file_sqlite3)
df.to_sql('patentdata',conn,if_exists='append',index=None)
conn.close()


#@title Agent作成
db = SQLDatabase.from_uri("sqlite:///patentdata.db")
toolkit = SQLDatabaseToolkit(db=db)

agent_executor = create_sql_agent(
    llm=OpenAI(temperature=0),
    toolkit=toolkit,
    verbose=True
)

• しかしデータが多いとそもそもsqliteに出力するのが大変なので、BigQueryに直接アクセスできないのか検討。
• この投稿を参考にさせてもらい、無事に完成！sqlalchemyとか知らないとできないですね。

3.コード

• 下記のような形。ちょっと面倒だったのがservice_account_fileをGCPのAPIs&Servicesで生成するところ。権限もBigQuery.createまでで制限しておいたほうがよさそう。
• あと、sqlalchemy_urlをよく見るとdatasetまで指定しておけばよいみたい。

#!pip install  sqlalchemy sqlalchemy-bigquery google-cloud-bigquery
from google.cloud import bigquery
from sqlalchemy import *
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import *
import os
from langchain.agents import create_sql_agent
from langchain.agents.agent_toolkits import SQLDatabaseToolkit
from langchain.sql_database import SQLDatabase
from langchain.llms.openai import OpenAI
from langchain.agents import AgentExecutor
service_account_file= '***.json'

project = "***"
dataset = "***"
table = "***"
sqlalchemy_url = f'bigquery://{project}/{dataset}?credentials_path={service_account_file}'

• あとはsqliteと同様。llmが賢いとDBの列名などから大体ほしい情報を考えて抽出してくれました。

db = SQLDatabase.from_uri(sqlalchemy_url)
llm = model
toolkit = SQLDatabaseToolkit(db=db, llm=llm)
agent_executor = create_sql_agent(
llm=llm,
toolkit=toolkit,
verbose=True,
top_k=10,
)

#agent_executor.run("技術分野にはどんなものがありますか？")
agent_executor.run("内視鏡分野への出願が多い出願人を教えてください。")

> Entering new  chain...
WARNING:langchain.chat_models.openai:Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 1.0 seconds as it raised ServiceUnavailableError: The server is overloaded or not ready yet..
Action: sql_db_list_tables
Action Input: 
Observation: status_table_1
Thought:I should check the schema of the status_table_1 to see if it contains information about applicants and endoscopy field applications.
Action: sql_db_schema
Action Input: ***
Observation: 
CREATE TABLE 
3 rows from *** table:

小林製薬株式会社	小林製薬株式会社	A61K35/60	A61K	A61K  35/	2012074217	425203693	5991836	2012	0.0	製薬	1.2721855959867483	0.5012662063816078	登録	2012074217	存続中	4C087	動物，微生物物質含有医薬	A61K35/60	権利存続中
ＯＭＣ株式会社	ＯＭＣ株式会社	A23N17/00	A23N	A23N  17/	2012062321	425192492	-	2012	0.0	その他の特殊機械	0.9140610153084134	0.5031655159540196	拒絶・取下等	None	None	4B061	果実・野菜の大量処理装置及び飼料調整装置	A23N17/00	拒絶・取下等
ビーエーエスエフ　ソシエタス・ヨーロピア	ビーエーエスエフ　ソシエタス・ヨーロピア	C08G65/06	C08G	C08G  65/	2013553938	WO2012110585	-	2012	0.0	高分子化学、ポリマー	1.1006605346978757	0.508230341480451	拒絶・取下等	None	None	4J005	ポリエーテル	C08G65/06	拒絶・取下等
*/
Thought:*** table contains information about applicants (APPLICANT) and IPC classification (ipc, subclass, maingroup). I should query this table to find the applicants with the most applications in the endoscopy field. I will limit the results to 10 and order by the count of applications in descending order. I need to check if the query is correct before executing it.
Action: sql_db_query_checker

Action Input: SELECT ***
Observation: The original query seems to be correct. There are no common mistakes found in the query. Here is the original query again:

Thought:The query is correct, so I can execute it to find the applicants with the most applications in the endoscopy field.
Action: sql_db_query
Action Input: SELECT ***
Observation: [('オリンパス株式会社', 7013), ('ＨＯＹＡ株式会社', 3251), ('富士フイルム株式会社', 2766), ('オリンパスメディカルシステムズ株式会社', 2153), ('コヴィディエン\u3000リミテッド\u3000パートナーシップ', 2038), ('エシコン・エンド－サージェリィ・インコーポレイテッド', 1172), ('エシコン\u3000エルエルシー', 1012), ('テルモ株式会社', 945), ('バイオセンス・ウエブスター・（イスラエル）・リミテッド', 484), ('ボストン\u3000サイエンティフィック\u3000サイムド，インコーポレイテッド', 476)]


Thought:I now know the final answer.
Final Answer: 内視鏡分野への出願が多い出願人は以下の通りです。
1. オリンパス株式会社: 7013件
2. ＨＯＹＡ株式会社: 3251件
3. 富士フイルム株式会社: 2766件
4. オリンパスメディカルシステムズ株式会社: 2153件
5. コヴィディエン　リミテッド　パートナーシップ: 2038件
6. エシコン・エンド－サージェリィ・インコーポレイテッド: 1172件
7. エシコン　エルエルシー: 1012件
8. テルモ株式会社: 945件
9. バイオセンス・ウエブスター・（イスラエル）・リミテッド: 484件
10. ボストン　サイエンティフィック　サイムド，インコーポレイテッド: 476件

> Finished chain.
内視鏡分野への出願が多い出願人は以下の通りです。\n1. オリンパス株式会社: 7013件\n2. ＨＯＹＡ株式会社: 3251件\n3. 富士フイルム株式会社: 2766件\n4. オリンパスメディカルシステムズ株式会社: 2153件\n5. コヴィディエン\u3000リミテッド\u3000パートナーシップ: 2038件\n6. エシコン・エンド－サージェリィ・インコーポレイテッド: 1172件\n7. エシコン\u3000エルエルシー: 1012件\n8. テルモ株式会社: 945件\n9. バイオセンス・ウエブスター・（イスラエル）・リミテッド: 484件\n10. ボストン\u3000サイエンティフィック\u3000サイムド，インコーポレイテッド: 476件