Select AIの使用例
OracleのSelect AIをサポートされている様々なAIプロバイダと統合して、自然言語プロンプトからSQLを生成、実行および説明したり、LLMとチャットしたりする方法をご紹介します。
- 例: AIアクションの選択
これらの例では、一般的なSelect AIアクションを示し、それらのアクションを活用するために様々なAIプロバイダを使用してプロファイルを設定する手順を示します。 - 例: OCI生成AIを使用したAIの選択
これらの例は、OCI APIキーまたはリソース・プリンシパルを使用してOCI生成AIにアクセスし、AIプロファイルを作成し、OCI生成AI LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示しています。 - 例: OpenAIを使用したAIの選択
この例では、OpenAIを使用して自然言語プロンプトからSQL文を生成する方法を示します。 - 例: Cohereを使用したAIの選択
この例は、Cohereを使用して自然言語プロンプトからSQL文を生成する方法を示しています。 - 例: Azure OpenAIサービスを使用したAIの選択
次の例は、APIキーを使用してAzure OpenAIサービスへのアクセスを有効にする方法、またはAzure OpenAIサービス・プリンシパルを使用したり、AIプロファイルを作成したり、自然言語プロンプトからSQLを生成する方法を示しています。 - 例: Googleを使用したAIの選択
この例は、Googleを使用して、Google Gemini LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示しています。 - 例: Anthropicを使用したAIの選択
この例は、Anthropic Claude LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示しています。 - 例: 顔を抱くAIの選択
この例は、Hugging Faceを使用して、自然言語プロンプトまたはHugging Face LLMを使用してチャットからSQLを生成、実行および説明する方法を示しています。 - 例: RAGを使用したSelect AIの設定および使用
この例では、資格証明の設定、ネットワーク・アクセスの構成、およびOracle Autonomous Databaseを使用してOCI生成AIベクトル・ストア・クラウド・サービスをOpenAIと統合するためのベクトル索引の作成について説明します。 - 例: 表および列コメントを使用したSQL問合せ生成の改善
この例では、データベース表および列のコメントによって、自然言語プロンプトからのSQL問合せの生成がどのように改善されるかを示します。 - 例: 合成データの生成
この例では、実データの特性と分布を模倣した合成データを生成する方法を探ります。
親トピック: Select AIを使用したデータベースとの自然言語対話
例: AIアクションの選択
これらの例では、一般的なSelect AIアクションを紹介し、それらのアクションを活用するために様々なAIプロバイダでプロファイルを設定する手順を示します。
次の例は、SELECT AIで実行できるrunsql、showsql、narrate、chat、explainsqlなどのアクションを示しています。これらの例では、DBMS_CLOUD_AI.CREATE_PROFILE関数で設定されたAIプロバイダおよびプロファイル属性でshスキーマを使用します。
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai showsql how many customers exist;
RESPONSE
----------------------------------------------------
SELECT COUNT(*) AS total_customers
FROM SH.CUSTOMERS
SQL> select ai narrate how many customers exist;
RESPONSE
------------------------------------------------------
There are a total of 55,500 customers in the database.
SQL> select ai chat how many customers exist;
RESPONSE
--------------------------------------------------------------------------------
It is impossible to determine the exact number of customers that exist as it con
stantly changes due to various factors such as population growth, new businesses
, and customer turnover. Additionally, the term "customer" can refer to individu
als, businesses, or organizations, making it difficult to provide a specific num
ber.
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.親トピック: Select AIの使用例
例: OCI生成AIを使用したAIの選択
これらの例は、OCI APIキーまたはリソース・プリンシパルを使用してOCI生成AIにアクセスし、AIプロファイルを作成し、OCI生成AI LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示しています。
ノート
model_nameを指定しない場合、OCI生成AIではmeta.llama-3-70b-instructがデフォルト・モデルとして使用されます。パラメータの詳細は、「プロファイル属性」を参照してください。
-- Create Credential with OCI API key
--
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaaa...',
tenancy_ocid => 'ocid1.tenancy.oc1..aaaa...',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
Number of Customers
-------------------
55500
SQL> select ai how many customers in San Francisco are married;
COUNT(DISTINCTC."CUST_ID")
--------------------------
28
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID")
FROM "SH"."CUSTOMERS" c
JOIN "SH"."COUNTRIES" co ON c."COUNTRY_ID" = co."COUNTRY_ID"
WHERE c."CUST_CITY" = 'San Francisco' AND c."CUST_MARITAL_STATUS" = 'married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
Here is the Oracle SQL query to find the number of customers in San Francisco who are married:
```
SELECT COUNT(*)
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco'
AND c."CUST_MARITAL_STATUS" = 'Married';
```
Explanation:
* We use the `COUNT(*)` aggregate function to count the number of rows that match the conditions.
* We specify the table alias `c` for the `"SH"."CUSTOMERS"` table to make the query more readable.
* We use the `WHERE` clause to filter the rows based on two conditions:
+ `c."CUST_CITY" = 'San Francisco'`: We filter the rows where the customer's city is San Francisco.
+ `c."CUST_MARITAL_STATUS" = 'Married'`: We filter the rows where the customer's marital status is Married.
* The double quotes around the table and column names are used to make the query case-sensitive, as required in Oracle SQL.
Note: The column names and table names are assumed to be case-sensitive, so we enclose them in double quotes. If the column names and table names are not case-sensitive, you can remove the double quotes.
Collapse
This snippet was truncated for display; see it in full
SQL> select ai narrate how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------
There are 28 customers in San Francisco who are married.
SQL> select ai chat what is Autonomous Database;
RESPONSE
An Autonomous Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the administrative and maintenance tasks typically performed by a database administrator (DBA). This allows the database to manage itself, without human intervention, to a large extent.
Autonomous databases are designed to be self-driving, self-securing, and self-repairing, which means they can:
1. **Automate administrative tasks**: Such as provisioning, patching, upgrading, and tuning, which frees up DBAs to focus on higher-level tasks.
2. **Optimize performance**: By automatically adjusting parameters, indexing, and caching to ensure optimal performance and efficiency.
3. **Detect and respond to security threats**: By using AI-powered security tools to identify and respond to potential security threats in real-time.
4. **Heal itself**: By automatically detecting and repairing errors, corruption, or other issues that may arise.
5. **Scale up or down**: To match changing workload demands, without the need for manual intervention.
The benefits of Autonomous Databases include:
1. **Increased efficiency**: By automating routine tasks, DBAs can focus on more strategic activities.
2. **Improved performance**: Autonomous databases can optimize performance in real-time, leading to faster query response times and better overall system performance.
3. **Enhanced security**: AI-powered security tools can detect and respond to threats more quickly and effectively than human administrators.
4. **Reduced costs**: By minimizing the need for manual intervention, Autonomous Databases can help reduce labor costs and improve resource utilization.
5. **Improved reliability**: Autonomous Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous Database is a popular example of an Autonomous Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous Database services as part of their cloud offerings.
In summary, Autonomous Databases are designed to be self-managing, self-optimizing, and self-healing, which can lead to improved performance, security, and efficiency, while reducing costs and administrative burdens.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.例: OCI生成AIリソース・プリンシパルを使用したAIの選択
OCI生成AIでリソース・プリンシパルを使用するには、Oracle Cloud Infrastructureテナンシ管理者が生成AIリソースのアクセス権を動的グループに付与する必要があります。動的グループへのアクセスを提供するには、Autonomous Databaseでリソース・プリンシパルを使用するための前提条件の実行を参照してください。
すべての生成AIリソースへのアクセスを取得するために必要なポリシーを設定します。生成AIポリシーの詳細は、生成AIへのアクセスを参照してください。
-
テナンシ全体のすべての生成AIリソースにアクセスするには、次のポリシーを使用します:
allow group <your-group-name> to manage generative-ai-family in tenancy -
コンパートメント内のすべての生成AIリソースにアクセスするには、次のポリシーを使用します:
allow group <your-group-name> to manage generative-ai-family in compartment <your-compartment-name>
管理者として接続し、OCIリソース・プリンシパルを有効にします。パラメータを構成するには、ENABLE_PRINCIPAL_AUTHプロシージャを参照してください。
ノート
modelを指定しない場合、OCI生成AIではmeta.llama-3-70b-instructがデフォルト・モデルとして使用されます。パラメータの詳細は、「プロファイル属性」を参照してください。
-- Connect as Administrator user and enable OCI resource principal.
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'OCI');
END;
/
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "OCI$RESOURCE_PRINCIPAL",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
Number of Customers
-------------------
55500
SQL> select ai how many customers in San Francisco are married;
COUNT(DISTINCTC."CUST_ID")
--------------------------
28
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID")
FROM "SH"."CUSTOMERS" c
JOIN "SH"."COUNTRIES" co ON c."COUNTRY_ID" = co."COUNTRY_ID"
WHERE c."CUST_CITY" = 'San Francisco' AND c."CUST_MARITAL_STATUS" = 'married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
Here is the Oracle SQL query to find the number of customers in San Francisco who are married:
```
SELECT COUNT(*)
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco'
AND c."CUST_MARITAL_STATUS" = 'Married';
```
Explanation:
* We use the `COUNT(*)` aggregate function to count the number of rows that match the conditions.
* We specify the table alias `c` for the `"SH"."CUSTOMERS"` table to make the query more readable.
* We use the `WHERE` clause to filter the rows based on two conditions:
+ `c."CUST_CITY" = 'San Francisco'`: We filter the rows where the customer's city is San Francisco.
+ `c."CUST_MARITAL_STATUS" = 'Married'`: We filter the rows where the customer's marital status is Married.
* The double quotes around the table and column names are used to make the query case-sensitive, as required in Oracle SQL.
Note: The column names and table names are assumed to be case-sensitive, so we enclose them in double quotes. If the column names and table names are not case-sensitive, you can remove the double quotes.
Collapse
This snippet was truncated for display; see it in full
SQL> select ai narrate how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------
There are 28 customers in San Francisco who are married.
SQL> select ai chat what is Autonomous Database;
RESPONSE
An Autonomous Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the administrative and maintenance tasks typically performed by a database administrator (DBA). This allows the database to manage itself, without human intervention, to a large extent.
Autonomous databases are designed to be self-driving, self-securing, and self-repairing, which means they can:
1. **Automate administrative tasks**: Such as provisioning, patching, upgrading, and tuning, which frees up DBAs to focus on higher-level tasks.
2. **Optimize performance**: By automatically adjusting parameters, indexing, and caching to ensure optimal performance and efficiency.
3. **Detect and respond to security threats**: By using AI-powered security tools to identify and respond to potential security threats in real-time.
4. **Heal itself**: By automatically detecting and repairing errors, corruption, or other issues that may arise.
5. **Scale up or down**: To match changing workload demands, without the need for manual intervention.
The benefits of Autonomous Databases include:
1. **Increased efficiency**: By automating routine tasks, DBAs can focus on more strategic activities.
2. **Improved performance**: Autonomous databases can optimize performance in real-time, leading to faster query response times and better overall system performance.
3. **Enhanced security**: AI-powered security tools can detect and respond to threats more quickly and effectively than human administrators.
4. **Reduced costs**: By minimizing the need for manual intervention, Autonomous Databases can help reduce labor costs and improve resource utilization.
5. **Improved reliability**: Autonomous Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous Database is a popular example of an Autonomous Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous Database services as part of their cloud offerings.
In summary, Autonomous Databases are designed to be self-managing, self-optimizing, and self-healing, which can lead to improved performance, security, and efficiency, while reducing costs and administrative burdens.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.例: LLAMAモデルを使用したOCI生成AIを使用したAIの選択
この例では、OCI生成AIからのLLAMAモデルのchat機能を示します。これは、2つのプロンプトによってモデルの機能を強調します。つまり、映画の推奨事項を顧客にわかりやすい電子メールを生成し、ロッククライミングの概要段落を生成します。
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaa',
tenancy_ocid => 'ocid1.tenancy.oc1..aaa',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'GENAI',
attributes =>'{"provider": "oci",
"object_list": [
{"owner": "ADB_USER", "name": "GENRE"},
{"owner": "ADB_USER", "name": "CUSTOMER"},
{"owner": "ADB_USER", "name": "PIZZA_SHOP"},
{"owner": "ADB_USER", "name": "STREAMS"},
{"owner": "ADB_USER", "name": "MOVIES"},
{"owner": "ADB_USER", "name": "ACTORS"}
]
"model": "meta.llama-2-70b-chat",
"oci_runtimetype":"LLAMA",
"credential_name": "GENAI_CRED",
"oci_compartment_id": "ocid1.compartment.oc1..."}');
END;
/
PL/SQL procedure successfully completed.
--
--Set profile
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
SQL> set linesize 150
SQL> SELECT AI chat Generate a friendly email to customer Gilbert Lee with two action-thriller movie recommendations available through our MovieStream service;
SQL>
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
.
Subject: Action-packed movie recommendations for you!
Dear Gilbert,
I hope this email finds you well! I wanted to reach out to you today to recommend two action-thriller movies that are currently available on our Movie
Stream service. I think you'll really enjoy them!
The first movie I recommend is "John Wick" starring Keanu Reeves. This movie follows the story of a retired hitman who seeks vengeance against a power
ful crime lord and his army of assassins. The action scenes are intense and non-stop, and Keanu Reeves delivers an outstanding performance.
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
The second movie I recommend is "Mission: Impossible - Fallout" starring Tom Cruise. This movie follows Ethan Hunt and his team as they try to prevent
a global catastrophe. The action scenes are heart-stopping and the stunts are truly impressive. Tom Cruise once again proves why he's one of the grea
test action stars of all time.
Both of these movies are sure to keep you on the edge of your seat and provide plenty of thrills and excitement. They're available to stream now on Mo
vieStream, so be sure to check them out!
If you have any questions or need assistance with MovieStream, please don't hesitate to reach out to me. I'm always here to help.
Thank you for being a valued customer, and I hope you enjoy the movies!
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
Best regards,
[Your Name]
MovieStream Customer Service
SQL> SELECT AI chat Write an enthusiastic introductory paragraph on how to get started with rock climbing with Athletes as the target audience;
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
Rock climbing is an exhilarating and challenging sport that's perfect for athletes looking to push their limits and test their strength, endurance, an
d mental toughness. Whether you're a seasoned athlete or just starting out, rock climbing offers a unique and rewarding experience that will have you
hooked from the very first climb. With its combination of physical and mental challenges, rock climbing is a great way to build strength, improve flex
ibility, and develop problem-solving skills. Plus, with the supportive community of climbers and the breathtaking views from the top of the climb, you
'll be hooked from the very first climb. So, if you're ready to take on a new challenge and experience the thrill of adventure, then it's time to get
started with rock climbing!デフォルト・モデルでのOCI生成AIの使用
次の例では、デフォルトのOCI生成AIチャット・モデルmeta.llama-3-70b-instructを使用します。modelを指定しない場合、OCI生成AIはmeta.llama-3-70b-instructをデフォルト・モデルとして使用します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_DEFAULT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}]
}');
END;
/チャット・モデルでのOCI生成AIの使用
次の例では、OCI生成AIチャット・モデルとしてcohere.command-r-plusを使用します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_COHERE_COMMAND_R_PLUS',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"model": "cohere.command-r-plus"
}');
END;
/チャット・モデル・エンドポイントIDでのOCI生成AIの使用
次の例では、modelのかわりにOCI生成AIチャット・モデルのエンドポイントIDを指定する方法を示します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_CHAT_ENDPOINT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"oci_endpoint_id": "<endpoint_id>",
"oci_apiformat": "GENERIC"
}');
END;
/チャット・モデルOCIDでのOCI生成AIの使用
この例では、OCI生成AIチャット・モデルOCIDをmodelとして指定する方法を示します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_CHAT_OCID',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"model": "<model_ocid>",
"oci_apiformat": "COHERE"
}');
END;
/生成モデルでのOCI生成AIの使用
この例では、cohere.commandなどのOCI生成AI生成モデルをmodelとして指定する方法を示します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_COHERE_COMMAND',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"model": "cohere.command"
}');
END;
/ 生成モデル・エンドポイントIDでのOCI生成AIの使用
この例では、modelのかわりにOCI生成AI生成モデルのエンドポイントIDを使用する方法を示します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENTEXT_ENDPOINT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"oci_endpoint_id": "<endpoint_id>"
"oci_runtimetype": "COHERE"
}');
END;
/生成モデルOCIDでのOCI生成AIの使用
この例では、OCI生成AI生成モデルOCIDをmodelとして指定する方法を示します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENTEXT_OCID',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"model": "<model_ocid>"
"oci_runtimetype": "LLAMA"
}');
END;
/親トピック: Select AIの使用例
例: OpenAIを使用したAIの選択
この例では、OpenAIを使用して自然言語プロンプトからSQL文を生成する方法を示します。
ノート
EXECUTE権限およびネットワークACLプロシージャを実行できるのは、DBAのみです。
--Grants EXECUTE privilege to ADB_USER
--
SQL> grant execute on DBMS_CLOUD_AI to ADB_USER;
-- Grant Network ACL for OpenAI endpoint
--
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create Credential for AI provider
--
SQL> EXEC DBMS_CLOUD.CREATE_CREDENTIAL('OPENAI_CRED', 'OPENAI', '<your api token>');
PL/SQL procedure successfully completed.
--
-- Create AI profile
--SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'OPENAI',
'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"OPENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('OPENAI');
PL/SQL procedure successfully completed.親トピック: Select AIの使用例
例: CohereでAIを選択
この例では、Cohereを使用して自然言語プロンプトからSQL文を生成する方法を示します。
ノート
EXECUTE権限およびネットワークACLプロシージャを実行できるのは、DBAのみです。
--Grants EXECUTE privilege to ADB_USER
--
SQL>grant execute on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL> EXEC DBMS_CLOUD.CREATE_CREDENTIAL('COHERE_CRED', 'COHERE', '<your api token>');
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Cohere endpoint
--
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.cohere.ai',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'COHERE',
'{"provider": "cohere",
"credential_name": "COHERE_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "sales"},
{"owner": "SH", "name": "products"},
{"owner": "SH", "name": "countries"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('COHERE');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"COHERE"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('COHERE');
PL/SQL procedure successfully completed.親トピック: Select AIの使用例
例: Azure OpenAI Serviceを使用したAIの選択
次の例では、APIキーを使用してAzure OpenAIサービスへのアクセスを有効にするか、Azure OpenAIサービス・プリンシパルを使用してAIプロファイルを作成し、自然言語プロンプトからSQLを生成する方法を示します。
-- Create Credential for AI integration
--
SQL> EXEC DBMS_CLOUD.CREATE_CREDENTIAL('AZURE_CRED', 'AZUREAI', '<your api token>');
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for OpenAI endpoint
--SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '<azure_resource_name>.openai.azure.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=> 'AZUREAI',
attributes=> '{"provider": "azure",
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
"credential_name": "AZURE_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.例: Azure OpenAIサービス・プリンシパルを使用したAIの選択
データベース管理者として接続してAzureサービス・プリンシパル認証へのアクセスを提供し、Select AIを使用するユーザー(ADB_USER)にネットワークACL権限を付与します。Azureリソースへのアクセスを提供するには、Azureサービス・プリンシパルを使用したAzureリソースへのアクセスを参照してください。
ノート
EXECUTE権限およびネットワークACLプロシージャを実行できるのは、DBAユーザーのみです。
-- Connect as ADMIN user and enable Azure service principal authentication.
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'AZURE',
params => JSON_OBJECT('azure_tenantid' value 'azure_tenantid'));
END;
/
-- Copy the consent url from cloud_integrations view and consents the ADB-S application.
SQL> select param_value from CLOUD_INTEGRATIONS where param_name = 'azure_consent_url';
PARAM_VALUE
--------------------------------------------------------------------------------
https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/authorize?client_id=<client_id>&response_type=code&scope=User.read
-- On the Azure OpenAI IAM console, search for the Azure application name and assign the permission to the application.
-- You can get the application name in the cloud_integrations view.
SQL> select param_value from CLOUD_INTEGRATIONS where param_name = 'azure_app_name';
PARAM_VALUE
--------------------------------------------------------------------------------
ADBS_APP_DATABASE_OCID
--
-- Grant Network ACL for Azure OpenAI endpoint
--SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'azure_resource_name.openai.azure.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'AZUREAI',
attributes=>'{"provider": "azure",
"credential_name": "AZURE$PA",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.親トピック: Select AIの使用例
例: GoogleでAIを選択
この例では、Googleを使用して、Google Gemini LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示します。
次の例は、GoogleをAIプロバイダとして使用する方法を示しています。この例では、Google API署名キーを使用してネットワーク・アクセスを提供し、AIプロファイルを作成し、Select AIアクションを使用して自然言語プロンプトおよびチャット・レスポンスからSQL問合せを生成する方法を示します。
--Grants EXECUTE privilege to ADB_USER
--
SQL> grant EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL> BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GOOGLE_CRED',
username => 'GOOGLE',
password => '<your_api_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Google endpoint
--
SQL>
SQL> BEGIN
DBMS_NETWORK_ACL_ADB_USER.APPEND_HOST_ACE(
host => 'generativelanguage.googleapis.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GOOGLE',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"object_list": [{"owner": "ADB_USER", "name": "users"},
{"owner": "ADB_USER", "name": "movies"},
{"owner": "ADB_USER", "name": "genres"},
{"owner": "ADB_USER", "name": "watch_history"},
{"owner": "ADB_USER", "name": "movie_genres"}
]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('GOOGLE');
PL/SQL procedure successfully completed.
--
-- Use AI
--
SQL> select ai list the year that most of the movies are released;
RELEASE_YEAR
------------
2020
1 row selected.
SQL> select ai showsql List movies watched by users but not in genre 'Comedy';
RESPONSE
--------------------------------------------------------------------------------
SELECT DISTINCT m.TITLE AS MOVIE_TITLE FROM "ADMIN"."MOVIES" m JOIN "ADMIN"."WAT
CH_HISTORY" wh ON m.MOVIE_ID = wh.MOVIE_ID JOIN "ADMIN"."MOVIE_GENRES" mg ON m.M
OVIE_ID = mg.MOVIE_ID JOIN "ADMIN"."GENRES" g ON mg.GENRE_ID = g.GENRE_ID WHERE
g.GENRE_NAME != 'Comedy'
1 row selected.
SQL> select ai showsql Show users who have watched at least one movie from each genre;
RESPONSE
--------------------------------------------------------------------------------
SELECT DISTINCT "ADMIN"."USERS"."USER_NAME" FROM "ADMIN"."USERS" JOIN "ADMIN"."W
ATCH_HISTORY" ON "ADMIN"."USERS"."USER_ID" = "ADMIN"."WATCH_HISTORY"."USER_ID" J
OIN "ADMIN"."MOVIES" ON "ADMIN"."WATCH_HISTORY"."MOVIE_ID" = "ADMIN"."MOVIES"."M
OVIE_ID" JOIN "ADMIN"."MOVIE_GENRES" ON "ADMIN"."MOVIES"."MOVIE_ID" = "ADMIN"."M
OVIE_GENRES"."MOVIE_ID" JOIN "ADMIN"."GENRES" ON "ADMIN"."MOVIE_GENRES"."GENRE_I
D" = "ADMIN"."GENRES"."GENRE_ID" GROUP BY "ADMIN"."USERS"."USER_NAME" HAVING COU
NT(DISTINCT "ADMIN"."GENRES"."GENRE_ID") = (SELECT COUNT(*) FROM "ADMIN"."GENRES
")
1 row selected.
SQL> select ai explainsql the top 3 most popular genres based on movie watch counts;
RESPONSE
--------------------------------------------------------------------------------
```sql
SELECT
g."GENRE_NAME" AS "Genre Name",
COUNT(DISTINCT wh."WATCH_ID") AS "Watch Count"
FROM
"ADMIN"."GENRES" g
JOIN
"ADMIN"."MOVIE_GENRES" mg ON g."GENRE_ID" = mg."GENRE_ID"
JOIN
"ADMIN"."MOVIES" m ON mg."MOVIE_ID" = m."MOVIE_ID"
JOIN
"ADMIN"."WATCH_HISTORY" wh ON m."MOVIE_ID" = wh."MOVIE_ID"
GROUP BY
g."GENRE_NAME"
ORDER BY
"Watch Count" DESC
FETCH FIRST 3 ROWS ONLY;
```
**Explanation:**
1. **FROM and JOIN Clauses:**
- We start by selecting data from the "GENRES" table (aliased as "g") to get
the genre names.
- We join it with "MOVIE_GENRES" (aliased as "mg") on the common column "GENR
E_ID" to link genres to movies.
- We join "MOVIE_GENRES" with "MOVIES" (aliased as "m") using "MOVIE_ID" to c
onnect movies to their genres.
- Finally, we join "MOVIES" with "WATCH_HISTORY" (aliased as "wh") using "MOV
IE_ID" to connect movies to watch history records.
2. **SELECT Clause:**
- `g."GENRE_NAME" AS "Genre Name"`: Selects the genre name from the "GENRES"
table and aliases it as "Genre Name".
- `COUNT(DISTINCT wh."WATCH_ID") AS "Watch Count"`: Counts the distinct watch
IDs to get the number of times movies in each genre have been watched. We use D
ISTINCT to avoid counting the same watch record multiple times if a movie belong
s to multiple genres.
3. **GROUP BY Clause:**
- We group the results by "Genre Name" to count watch counts for each distinc
t genre.
4. **ORDER BY Clause:**
- `ORDER BY "Watch Count" DESC`: Sorts the result set in descending order bas
ed on the "Watch Count" column to get the most popular genres first.
5. **FETCH FIRST 3 ROWS ONLY:**
- This clause limits the result set to the top 3 rows, effectively returning
only the top 3 most popular genres.
**Key Points:**
- **Case Sensitivity:** Oracle SQL is case-sensitive. We use double quotes to en
close table and column names to preserve their original casing.
- **Table and Column Aliases:** Using aliases makes the query more readable and
concise.
- **DISTINCT:** Ensures accurate watch count by eliminating duplicates from the
watch history records.
- **ORDER BY and FETCH FIRST:** These clauses effectively retrieve the top 3 mos
t popular genres.
**Key Points:**
- **Case Sensitivity:** Oracle SQL is case-sensitive. We use double quotes to en
close table and column names to preserve their original casing.
- **Table and Column Aliases:** Using aliases makes the query more readable and
concise.
- **DISTINCT:** Ensures accurate watch count by eliminating duplicates from the
watch history records.
- **ORDER BY and FETCH FIRST:** These clauses effectively retrieve the top 3 mos
t popular genres.
1 row selected.
SQL> select ai narrate Show the average and total watch counts per genre;
RESPONSE
--------------------------------------------------------------------------------
The answer shows the total and average number of times movies belonging to each
genre were watched.
For example:
- Action genre movies were watched 3 times in total and the average watch count
for Action genre movies is 3.
- Comedy genre movies were watched 3 times in total and the average watch count
for Comedy genre movies is 3.
- Drama genre movies were watched 2 times in total and the average watch count f
or Drama genre movies is 2.
1 row selected.
SQL> select ai chat how long the history of the movie industry is;
RESPONSE
--------------------------------------------------------------------------------
The history of the movie industry is long and complex, spanning over **130 years
**.
Here's a quick timeline to give you an idea:
* **1890s:** The first motion pictures were created, leading to the development
of early film cameras and projectors.
* **1900s:** The first movie theaters opened, and early film studios began to em
erge.
* **1910s:** The rise of Hollywood as a center for film production, and the deve
lopment of narrative storytelling in film.
* **1920s:** The introduction of sound to film, marking a significant shift in t
he industry.
* **1930s:** The Golden Age of Hollywood, with the rise of iconic stars and stud
ios.
* **1940s:** The industry grapples with World War II and the rise of television.
* **1950s:** The introduction of color film and wide-screen formats.
* **1960s:** The rise of independent cinema and the New Hollywood era.
* **1970s:** The rise of blockbuster movies and the influence of Hollywood on gl
obal culture.
* **1980s:** The rise of home video and the increasing influence of technology i
n filmmaking.
* **1990s:** The digital revolution in film and the emergence of new distributio
n platforms.
* **2000s:** The rise of streaming services and the continued impact of technolo
gy on the industry.
The movie industry has gone through many changes throughout its history, but its
impact on culture and entertainment remains undeniable.
1 row selected.親トピック: Select AIの使用例
例: 人類学によるAIの選択
この例では、Anthropicを使用して、Anthropic Claude LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示します。
次の例は、AIプロバイダとしてのAnthropicの使用を示しています。この例では、Anthropic API署名キーを使用してネットワーク・アクセスを提供し、AIプロファイルを作成し、Select AIアクションを使用して、Anthropic Claude LLMを使用して自然言語プロンプトおよびチャットからSQL問合せを生成する方法を示します。
プロファイル属性を指定するには、「プロファイル属性」を参照してください。
--Grant EXECUTE privilege to ADB_USER
SQL>GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL>BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'ANTHROPIC_CRED',
username => 'ANTHROPIC',
password => '<your api key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Anthropic endpoint
--
SQL>BEGIN
DBMS_NETWORK_ACL_ADB_USER.APPEND_HOST_ACE(
host => 'api.anthropic.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL>BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'ANTHROPIC',
attributes =>'{"provider": "anthropic",
"credential_name": "ANTHROPIC_CRED",
"object_list": [{"owner": "ADB_USER", "name": "users"},
{"owner": "ADB_USER", "name": "movies"},
{"owner": "ADB_USER", "name": "genres"},
{"owner": "ADB_USER", "name": "watch_history"},
{"owner": "ADB_USER", "name": "movie_genres"}
]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE('ANTHROPIC');
PL/SQL procedure successfully completed.
--
-- Use AI
--
SQL>SELECT AI list all genres;
GENRE_ID GENRE_NAME
---------- --------------------------------------------------
1 Action
2 Comedy
3 Drama
3 rows selected.
SQL>SELECT AI showsql List the movie titles and their release dates for movies that hav
e a duration greater than 120 minutes;
RESPONSE
--------------------------------------------------------------------------------
SELECT m."TITLE" AS movie_title, m."RELEASE_DATE" AS release_date
FROM "ADB_USER"."MOVIES" m
WHERE m."DURATION" > 120
ORDER BY m."RELEASE_DATE"
1 row selected.
SQL>SELECT AI showsql List movies watched by users but not in genre 'Comedy';
RESPONSE
--------------------------------------------------------------------------------
SELECT DISTINCT m."TITLE" AS movie_title, u."USER_NAME" AS viewer
FROM "ADB_USER"."MOVIES" m
JOIN "ADB_USER"."WATCH_HISTORY" wh ON m."MOVIE_ID" = wh."MOVIE_ID"
JOIN "ADB_USER"."USERS" u ON wh."USER_ID" = u."USER_ID"
WHERE m."MOVIE_ID" NOT IN (
SELECT mg."MOVIE_ID"
FROM "ADB_USER"."MOVIE_GENRES" mg
JOIN "ADB_USER"."GENRES" g ON mg."GENRE_ID" = g."GENRE_ID"
WHERE g."GENRE_NAME" = 'Comedy'
)
ORDER BY m."TITLE", u."USER_NAME"
SQL>SELECT AI explainsql Find the unique user names and the count of movies they have w
atched;
RESPONSE
--------------------------------------------------------------------------------
Here's the Oracle SQL query to find the unique user names and the count of movie
s they have watched, along with a detailed explanation:
```sql
SELECT
u."USER_NAME",
COUNT(DISTINCT wh."MOVIE_ID") AS movies_watched_count
FROM
"ADB_USER"."USERS" u
LEFT JOIN
"ADB_USER"."WATCH_HISTORY" wh ON u."USER_ID" = wh."USER_ID"
GROUP BY
u."USER_NAME"
ORDER BY
movies_watched_count DESC;
```
Explanation:
1. FROM clause:
- We start with the "ADB_USER"."USERS" table and alias it as 'u'.
2. LEFT JOIN:
- We use a LEFT JOIN with the "ADB_USER"."WATCH_HISTORY" table (aliased as 'wh')
to include all users, even those who haven't watched any movies.
- The join condition is u."USER_ID" = wh."USER_ID", linking users to their wa
tch history.
3. SELECT clause:
- We select u."USER_NAME" to get the unique user names.
- We use COUNT(DISTINCT wh."MOVIE_ID") to count the number of unique movies w
atched by each user. This is aliased as 'movies_watched_count'.
- The DISTINCT keyword ensures that if a user has watched the same movie mult
iple times, it's only counted once.
4. GROUP BY clause:
- We group the results by u."USER_NAME" to get one row per unique user.
5. ORDER BY clause:
- We order the results by movies_watched_count in descending order (DESC) to
show users who have watched the most movies first.
This query will return a list of all users and the number of unique movies they
have watched, including users who haven't watched any movies (they will have a c
ount of 0 or NULL). The results will be sorted with the most active users (those
who have watched the most movies) at the top.
1 row selected.
SQL>SELECT AI narrate Show the average and total watch counts per genre;
RESPONSE
--------------------------------------------------------------------------------
This result shows the watch statistics for different movie genres:
- Action and Comedy genres are the most popular, each with 3 total watches and a
n average of 3 watches per movie.
- Drama genre follows with 2 total watches and an average of 2 watches per movie
.
These numbers indicate how often movies in each genre are being watched by users
, giving insight into genre popularity and viewing trends.
1 row selected.
SQL>SELECT AI chat which movie is the most famous;
RESPONSE
--------------------------------------------------------------------------------
It's difficult to definitively say which movie is the most famous, as popularity
and fame can be measured in different ways and can vary across cultures and tim
e periods. However, some movies that are widely considered to be among the most
famous globally include:
1. "Gone with the Wind" (1939)
2. "Star Wars: Episode IV - A New Hope" (1977)
3. "Titanic" (1997)
4. "The Godfather" (1972)
5. "E.T. the Extra-Terrestrial" (1982)
6. "Jaws" (1975)
7. "Avatar" (2009)
8. "The Wizard of Oz" (1939)
9. "Casablanca" (1942)
10. "Jurassic Park" (1993)
These films are renowned for their cultural impact, box office success, critical
acclaim, and enduring popularity. However, the concept of "most famous" can be
subjective and may change over time as new films are released and gain recogniti
on.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('ANTHROPIC');
PL/SQL procedure successfully completed.
親トピック: Select AIの使用例
例: Hugging Faceを使用したAIの選択
この例では、Hugging Faceを使用して、Hugging Face LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示します。
次の例は、AIプロバイダとしてのHugging Faceの使用を示しています。この例では、Hugging Face API署名キーを使用してネットワーク・アクセスを提供し、AIプロファイルを作成し、Select AIアクションを使用して自然言語プロンプトからSQL問合せを生成し、Hugging Face LLMを使用してチャットする方法を示します。
--Grant EXECUTE privilege to ADB_USER
SQL>GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL>BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'HF_CRED',
username => 'HF',
password => '<your_api_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Hugging Face endpoint
--
SQL>BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api-inference.huggingface.co',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL>BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'HF',
attributes =>'{"provider": "huggingface",
"credential_name": "HF_CRED",
"object_list": [{"owner": "ADB_USER", "name": "users"},
{"owner": "ADB_USER", "name": "moviesdata"}],
"model" : "mistralai/Mixtral-8x7B-Instruct-v0.1"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE('HF');
PL/SQL procedure successfully completed.
--
-- Use AI
--
SQL>SELECT AI list all genres;
GENRE_ID GENRE_NAME
---------- --------------------------------------------------
1 Action
2 Comedy
3 Drama
SQL>SELECT AI showsql List movies watched by users but not in genre 'Comedy';
SELECT DISTINCT m.TITLE
FROM "ADMIN"."MOVIE_SALES_FACT" m
WHERE m.GENRE NOT IN ('Comedy')
)
SQL>SELECT AI explainsql Find the unique user names and the count of movies they have watched;
RESPONSE
--------------------------------------------------------------------------------------------
Sure, here's an example of how you can find the unique user names and the count of movies they have watched using Oracle SQL and table aliases:
```sql
SELECT u.user_name, COUNT(DISTINCT m.movie_id) as movie_count
FROM ADMIN.USERS u
JOIN ADMIN.WATCH_HISTORY wh ON u.user_id = wh.user_id
JOIN ADMIN.MOVIES m ON wh.movie_id = m.movie_id
GROUP BY u.user_name;
```
Explanation:
1. We start by selecting the user name (`u.user_name`) and the count of distinct movie IDs (`COUNT(DISTINCT m.movie_id)`) as `movie_count` from the `USERS` table (aliased as `u`).
2. We then join the `USERS` table with the `WATCH_HISTORY` table (aliased as `wh`) on the `user_id` column. This gives us a dataset of all the movies watched by each user.
3. We further join the resulting dataset with the `MOVIES` table (aliased as `m`) on the `movie_id` column. This allows us to get the actual movie details for each movie ID.
4. Finally, we group the resulting dataset by the `user_name` column and apply the `COUNT` function to get the count of unique movies watched by each user.
Note that we have used table aliases (`u`, `wh`, and `m`) to make the query easier to read and write. We have also enclosed the schema name (`ADMIN`) and column names (`user_name`, `movie_id`) in double quotes to ensure that they are treated as case-sensitive.
To find the unique user names and the count of movies they have watched, you can use the following Oracle SQL query:
```vbnet
SELECT
u.USERNAME,
COUNT(m.ORDER_NUM) AS MOVIE_COUNT
FROM
ADMIN.MOVIE_SALES_FACT m
JOIN
ADMIN.CUSTOMER c ON m.CUSTOMER_ID = c.CUSTOMER_ID
JOIN
ADMIN.USER u ON c.USERNAME = u.USERNAME
GROUP BY
u.USERNAME;
```
Explanation:
1. We start by selecting the `USERNAME` column from the `ADMIN.USER` table and the count of movies watched by each user, which we calculate using the `COUNT` function and the `ORDER_NUM` column from the `ADMIN.MOVIE_SALES_FACT` table.
2. We then join the `ADMIN.MOVIE_SALES_FACT` table with the `ADMIN.CUSTOMER` table on the `CUSTOMER_ID` column, which allows us to associate each movie sale with a specific customer.
3. Next, we join the `ADMIN.CUSTOMER` table with the `ADMIN.USER` table on the `USERNAME` column, which allows us to associate each customer with a specific user.
4. Finally, we group the results by `USERNAME` using the `GROUP BY` clause, which allows us to count the number of movies watched by each user.
Using table aliases (`m`, `c`, and `u`) makes the query easier to read and write, especially when dealing with large and complex table structures. Additionally, enclosing table and column names in double quotes ensures that they are case sensitive, which is important in Oracle SQL.
SQL>SELECT AI narrate Show the average and total watch counts per genre;
The result provides the average and total watch counts per movie genre. For each genre, there is one record with two properties: "AVERAGE\_WATCH\_COUNT" and "TOTAL\_WATCH\_COUNT". The "AVERAGE\_WATCH\_COUNT" represents the average number of times a movie in that genre has been watched, while the "TOTAL\_WATCH\_COUNT" indicates the total number of times all movies in that genre have been watched.
Here are the genres and their corresponding average and total watch counts:
* Family: Average watch count is 1, with a total of 5,681,078 watches
* Mystery: Average watch count is 1, with a total of 833,989 watches
* Documentary: Average watch count is 1, with a total of 59,297 watches
* Adventure: Average watch count is 1, with a total of 13,414,954 watches
* Sport: Average watch count is 1, with a total of 931,251 watches
* News: Average watch count is 1, with a total of 32,090 watches
* Fantasy: Average watch count is 1, with a total of 5,115,105 watches
* Film-Noir: Average watch count is 1, with a total of 2,830,396 watches
* Romance: Average watch count is 1, with a total of 4,046,663 watches
* Horror: Average watch count is 1, with a total of 4,560,312 watches
* Thriller: Average watch count is 1, with a total of 6,395,926 watches
* History: Average watch count is 1, with a total of 1,182,406 watches
* Biography: Average watch count is 1, with a total of 1,191,253 watches
* Reality-TV: Average watch count is 1, with a total of 346,821 watches
* Comedy: Average watch count is 1, with a total of 11,645,917 watches
* Drama: Average watch count is 1, with a total of 18,577,932 watches
* Sci-Fi: Average watch count is 1, with a total of 7,885,454 watches
* Crime: Average watch count is 1, with a total of 5,406,563 watches
* Musical: Average watch count is 1, with a total of 2,935,625 watches
* War: Average watch count is 1, with a total of 4,237,470 watches
Note that the average watch count is 1 for all genres, meaning each movie in these genres has been watched at least once.
SQL>SELECT AI chat which movie is the most famous;
It's difficult to determine the most "famous" movie of all time, as fame can be subjective and depends on cultural, demographic, and personal factors. However, some of the most critically acclaimed and commercially successful movies in history include:
* The Godfather (1972)
* The Shawshank Redemption (1994)
* Pulp Fiction (1994)
* The Dark Knight (2008)
* Titanic (1997)
* Star Wars (1977)
* The Lord of the Rings: The Return of the King (2003)
* Inception (2010)
* Schindler's List (1993)
* Casablanca (1942)
These movies have had a significant impact on the film industry and have been widely recognized for their storytelling, direction, acting, and cultural significance.親トピック: Select AIの使用例
例: RAGでのSelect AIの設定および使用
この例では、Oracle Autonomous Databaseを使用してOCI生成AIベクトル・ストア・クラウド・サービスをOpenAIと統合するための資格証明の設定、ネットワーク・アクセスの構成およびベクトル索引の作成を順を追って説明します。
この設定は、ベクトル索引を使用してLLMレスポンスを拡張するAIプロファイルの作成で終わります。最後に、この例では、指定されたベクトル・データベースの情報を使用して拡張されたレスポンスを返す「AIの選択」narrateアクションを使用します。
次の例は、Oracle 23aiでのベクトル索引の構築および問合せを示しています。
SQL> -- Create the OpenAI credential
SQL> BEGIN
2 DBMS_CLOUD.CREATE_CREDENTIAL(
3 credential_name => 'OPENAI_CRED',
4 username => 'OPENAI_CRED',
5 password => '<your_api_key>'
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- Append the OpenAI endpoint
SQL> BEGIN
2 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
3 host => 'api.openai.com',
4 ace => xs$ace_type(privilege_list => xs$name_list('http'),
5 principal_name => 'ADMIN',
6 principal_type => xs_acl.ptype_db)
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- Create the object store credential
SQL> BEGIN
2 DBMS_CLOUD.CREATE_CREDENTIAL(
3 credential_name => 'OCI_CRED',
4 username => '<your_username>',
5 password => '<OCI_profile_password>'
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> -- Create the profile with the vector index.
SQL> BEGIN
2 DBMS_CLOUD_AI.CREATE_PROFILE(
3 profile_name =>'OPENAI_ORACLE',
4 attributes =>'{"provider": "openai",
5 "credential_name": "OPENAI_CRED",
6 "vector_index_name": "MY_INDEX",
7 "temperature": 0.2,
8 "max_tokens": 4096,
9 "model": "gpt-3.5-turbo-1106"
10 }');
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> -- Set profile
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI_ORACLE');
PL/SQL procedure successfully completed.
SQL> -- create a vector index with the vector store name, object store location and
SQL> -- object store credential
SQL> BEGIN
DBMS_CLOUD_AI.CREATE_VECTOR_INDEX(
index_name => 'MY_INDEX',
attributes => '{"vector_db_provider": "oracle",
"location": "https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/my_namespace/my_bucket/my_data_folder",
"object_storage_credential_name": "OCI_CRED",
"profile_name": "OPENAI_ORACLE",
"vector_dimension": 1536,
"vector_distance_metric": "cosine",
"chunk_overlap":128,
"chunk_size":1024
}');
END;
/
PL/SQL procedure successfully completed.
SQL> -- After the vector index is populated, we can now query the index.
SQL> -- Set profile
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI_ORACLE');
PL/SQL procedure successfully completed.
SQL> -- Select AI answers the question with the knowledge available in the vector database.
SQL> set pages 1000
SQL> set linesize 150
SQL> select AI narrate how can I deploy an oracle machine learning model;
RESPONSE
To deploy an Oracle Machine Learning model, you would first build your model within the Oracle database. Once your in-database models are built, they become immediately available for use, for instance, through a SQL query using the prediction operators built into the SQL language.
The model scoring, like model building, occurs directly in the database, eliminating the need for a separate engine or environment within which the model and corresponding algorithm code operate. You can also use models from a different schema (user account) if the appropriate permissions are in place.
Sources:
- Manage-your-models-with-Oracle-Machine-Learning-on-Autonomous-Database.txt (https://objectstorage.../v1/my_namespace/my_bucket/my_data_folder)
- Develop-and-deploy-machine-learning-models-using-Oracle-Autonomous-Database-Machine-Learning-and-APEX.txt (https://objectstorage.../v1/my_namespace/my_bucket/my_data_folder)
親トピック: Select AIの使用例
例: 表および列コメントを使用したSQL問合せ生成の改善
この例では、データベース表および列のコメントによって、自然言語プロンプトからのSQL問合せの生成がどのように改善されるかを示します。
この例では、Azure OpenAI ServiceがAIプロバイダとして機能します。
DBMS_CLOUD_AI.CREATE_PROFILEファンクションの"comments":"true"パラメータは、コメントがSQL生成のモデルに渡されるかどうかを決定します。
-- Adding comments to table 1, table 2, and table 3. Table 1 has 3 columns, table 2 has 7 columns, table 3 has 2 columns.
-- TABLE1
COMMENT ON TABLE table1 IS 'Contains movies, movie titles and the year it was released';
COMMENT ON COLUMN table1.c1 IS 'movie ids. Use this column to join to other tables';
COMMENT ON COLUMN table1.c2 IS 'movie titles';
COMMENT ON COLUMN table1.c3 IS 'year the movie was released';
-- TABLE2
COMMENT ON TABLE table2 IS 'transactions for movie views - also known as streams';
COMMENT ON COLUMN table2.c1 IS 'day the movie was streamed';
COMMENT ON COLUMN table2.c2 IS 'genre ids. Use this column to join to other tables';
COMMENT ON COLUMN table2.c3 IS 'movie ids. Use this column to join to other tables';
COMMENT ON COLUMN table2.c4 IS 'customer ids. Use this column to join to other tables';
COMMENT ON COLUMN table2.c5 IS 'device used to stream, watch or view the movie';
COMMENT ON COLUMN table2.c6 IS 'sales from the movie';
COMMENT ON COLUMN table2.c7 IS 'number of views, watched, streamed';
-- TABLE3
COMMENT ON TABLE table3 IS 'Contains the genres';
COMMENT ON COLUMN table3.c1 IS 'genre id. use this column to join to other tables';
COMMENT ON COLUMN table3.c2 IS 'name of the genre';
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'myprofile',
attributes =>
'{"provider": "azure",
"azure_resource_name": "my_resource",
"azure_deployment_name": "my_deployment",
"credential_name": "my_credential",
"comments":"true",
"object_list": [
{"owner": "moviestream", "name": "table1"},
{"owner": "moviestream", "name": "table2"},
{"owner": " moviestream", "name": "table3"}
]
}'
);
DBMS_CLOUD_AI.SET_PROFILE(
profile_name => 'myprofile'
);
END;
/
--Prompts
select ai what are our total views;
RESPONSE
-------------------------------------------------
TOTAL_VIEWS
-----------
97890562
select ai showsql what are our total views;
RESPONSE
-------------------------------------------------------------------------
SELECT SUM(QUANTITY_SOLD) AS total_views
FROM "moviestream"."table"
select ai what are our total views broken out by device;
DEVICE TOTAL_VIEWS
-------------------------- -----------
mac 14719238
iphone 20793516
ipad 15890590
pc 14715169
galaxy 10587343
pixel 10593551
lenovo 5294239
fire 5296916
8 rows selected.
select ai showsql what are our total views broken out by device;
RESPONSE
---------------------------------------------------------------------------------------
SELECT DEVICE, COUNT(*) AS TOTAL_VIEWS
FROM "moviestream"."table"
GROUP BY DEVICE親トピック: Select AIの使用例
例: 合成データの生成
この例では、実データの特性と分布を模倣した合成データを生成する方法を探ります。
次の例では、スキーマにいくつかの表を作成し、AIプロバイダとしてOCI生成AIを使用してAIプロファイルを作成し、DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA関数を使用してそれらの表にデータを合成し、Select AIを使用して自然言語プロンプトに対して問合せまたはレスポンスを生成する方法を示します。
--Create tables or use cloned tables
CREATE TABLE ADB_USER.Director (
director_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE ADB_USER.Movie (
movie_id INT PRIMARY KEY,
title VARCHAR(100),
release_date DATE,
genre VARCHAR(50),
director_id INT,
FOREIGN KEY (director_id) REFERENCES ADB_USER.Director(director_id)
);
CREATE TABLE ADB_USER.Actor (
actor_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE ADB_USER.Movie_Actor (
movie_id INT,
actor_id INT,
PRIMARY KEY (movie_id, actor_id),
FOREIGN KEY (movie_id) REFERENCES ADB_USER.Movie(movie_id),
FOREIGN KEY (actor_id) REFERENCES ADB_USER.Actor(actor_id)
);
-- Create the GenAI credential
BEGIN
DBMS_CLOUD.create_credential(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1....',
tenancy_ocid => 'ocid1.tenancy.oc1....',
private_key => 'vZ6cO...',
fingerprint => '86:7d:...'
);
END;
/
-- Create a profile
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"object_list": [{"owner": "ADB_USER",
"oci_compartment_id": "ocid1.compartment.oc1...."}]
}');
END;
/
EXEC DBMS_CLOUD_AI.set_profile('GENAI');
-- Run the API for single table
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Director',
owner_name => 'ADB_USER',
record_count => 5
);
END;
/
PL/SQL procedure successfully completed.
-- Query the table to see results
SQL> SELECT * FROM ADB_USER.Director;
DIRECTOR_ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 John Smith
2 Emily Chen
3 Michael Brown
4 Sarah Taylor
5 David Lee
-- Or ask select ai to show the results
SQL> select ai how many directors are there;
NUMBER_OF_DIRECTORS
-------------------
5
例: 複数の表に対する合成データの生成
AIプロバイダ・プロファイルを作成して設定した後、DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATAを使用して複数の表のデータを生成します。「AIの選択」を問い合せたり、使用して自然言語プロンプトに応答できます。
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_list => '[{"owner": "ADB_USER", "name": "Director","record_count":5},
{"owner": "ADB_USER", "name": "Movie_Actor","record_count":5},
{"owner": "ADB_USER", "name": "Actor","record_count":10},
{"owner": "ADB_USER", "name": "Movie","record_count":5,"user_prompt":"all movies released in 2009"}]'
);
END;
/
PL/SQL procedure successfully completed.
-- Query the table to see results
SQL> select * from ADB_USER.Movie;
MOVIE_ID TITLE RELEASE_D GENRE DIRECTOR_ID
---------- -------------------------------------------------------- --------- --------------------------------------------------------------- -----------
1 The Dark Knight 15-JUL-09 Action 8
2 Inglourious Basterds 21-AUG-09 War 3
3 Up in the Air 04-SEP-09 Drama 6
4 The Hangover 05-JUN-09 Comedy 1
5 District 9 14-AUG-09 Science Fiction 10
-- Or ask select ai to show the results
SQL> select ai how many actors are there;
Number of Actors
----------------
10例: サンプル行を使用した合成データ生成のガイド
合成データの生成でAIサービスをガイドするために、表から既存のレコードをランダムに選択できます。たとえば、params引数に{"sample_rows": 5}を追加すると、表からAIプロバイダに5つのサンプル行を送信できます。この例では、Transactions表のサンプル行に基づいて10行追加を生成します。
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Transactions',
owner_name => 'ADB_USER',
record_count => 10,
params => '{"sample_rows":5}'
);
END;
/例: ユーザー・プロンプトを使用した合成データ生成のカスタマイズ
user_prompt引数を使用すると、データ生成のための追加のルールまたは要件を指定できます。これは、単一の表に適用することも、複数の表のobject_list引数の一部として適用することもできます。たとえば、DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATAへの次のコールでは、2009年にリリースされたムービーで合成データを生成するようAIに指示します。
-- Definition for the Movie table CREATE TABLE Movie
CREATE TABLE Movie (
movie_id INT PRIMARY KEY,
title VARCHAR(100),
release_date DATE,
genre VARCHAR(50),
director_id INT,
FOREIGN KEY (director_id) REFERENCES Director(director_id)
);
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Movie',
owner_name => 'ADB_USER',
record_count => 10,
user_prompt => 'all movies are released in 2009',
params => '{"sample_rows":5}'
);
END;
/
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_list => '[{"owner": "ADB_USER", "name": "Director","record_count":5},
{"owner": "ADB_USER", "name": "Movie_Actor","record_count":5},
{"owner": "ADB_USER", "name": "Actor","record_count":10},
{"owner": "ADB_USER", "name": "Movie","record_count":5,"user_prompt":"all movies are released in 2009"}]'
);
END;
/例: 表統計を使用した合成データ品質の向上
表に列統計があるか、メタデータを含むデータベースからクローニングされている場合、Select AIはこれらの統計を使用して、元のデータとよく似ているか、または一貫性のあるデータを生成できます。
NUMBER列の場合、統計の上限値と下限値は値の範囲をガイドします。たとえば、元のEMPLOYEES表のSALARY列の範囲が1000から10000の場合、この列の合成データもこの範囲内に収まります。
値がCA、WAおよびTXのSTATE列など、個別値を持つ列の場合、合成データではこれらの特定の値が使用されます。この機能は、{"table_statistics": true/false}パラメータを使用して管理できます。デフォルトでは、表統計は有効です。
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Movie',
owner_name => 'ADB_USER',
record_count => 10,
user_prompt => 'all movies released in 2009',
params => '{"sample_rows":5,"table_statistics":true}'
);
END;
/例: 列コメントを使用したデータ生成のガイド
列コメントが存在する場合、「AIの選択」には、データ生成時にLLMの追加情報を提供するために自動的に含まれます。たとえば、トランザクション表のStatus列のコメントには、successful、failed、pending、canceled、need manual checkなどの許容値がリストされます。また、コメントを追加して列をさらに説明し、正確なデータを生成するためのより正確な指示やヒントをAIサービスに提供することもできます。デフォルトでは、コメントは無効です。詳細は、オプション・パラメータを参照してください。
-- Use comment on column
COMMENT ON COLUMN Transaction.status IS 'the value for state should either be ''successful'', ''failed'', ''pending'' or ''canceled''';
/
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'employees',
owner_name => 'ADB_USER',
record_count => 10
params => '{"comments":true}'
);
END;
/例: 合成データ生成での一意の値の設定
大量の合成データをLLMで生成する場合、重複する値が発生する可能性があります。これを防ぐには、関連する列に一意制約を設定します。これにより、LLMレスポンスで重複値を持つ行がSelect AIによって無視されます。また、特定の列の値を制限するには、user_promptを使用するか、コメントを追加して、STATE列をCA、WAおよびTXに制限するなど、許可される値を指定できます。
-- Use 'user_prompt'
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'employees',
owner_name => 'ADB_USER',
user_prompt => 'the value for state should either be CA, WA, or TX',
record_count => 10
);
END;
/
-- Use comment on column
COMMENT ON COLUMN EMPLOYEES.state IS 'the value for state should either be CA, WA, or TX'
/例: パラレル処理による合成データ生成の拡張
実行時間を短縮するために、Select AIは、合成データ生成タスクを、主キーのない表または数値の主キーを持つ表の小さなチャンクに分割します。これらのタスクは並行して実行され、AIプロバイダと対話してデータをより効率的に生成します。Autonomous Databaseサービス・レベルおよびECPUまたはOCPU設定の影響を受けるデータベース内の並列度(DOP)によって、各チャンク・プロセスのレコード数が決まります。通常、タスクを並行して実行すると、パフォーマンスが向上します。特に、多数の表にわたって大量のデータを生成する場合です。合成データ生成のパラレル処理を管理するには、オプションのパラメータとしてpriorityを設定します。オプション・パラメータを参照してください。
親トピック: Select AIの使用例