PolarDB:Build a Data-Agent using an external data source

Step 1: Log in with a privileged account

Connect to the cluster using a privileged account.

Step 2: Get the node token

In the PolarDB console, go to the target cluster's product page. In the Database Nodes section, find the AI node, click View, and record the Node Token.

Step 3: Install the extension and configure the node token

-- Install the extension
CREATE EXTENSION polar_ai;

-- Set the node token
SELECT polar_ai._ai_nl2sql_alter_token('sk-xxx');

Step 4: Create the metadata table

The schema_info table stores information about your external data sources: table names, column names, data types, comments, and sample values.

CREATE TABLE IF NOT EXISTS public.schema_info (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(255) NOT NULL,
    table_comment TEXT,
    column_name VARCHAR(255) NOT NULL,
    column_comment TEXT,
    data_type VARCHAR(255) NOT NULL,
    sample_values TEXT,
    is_primary BOOLEAN DEFAULT FALSE,
    is_foreign BOOLEAN DEFAULT FALSE,
    ext TEXT,
    db_type VARCHAR(128)
);

COMMENT ON TABLE schema_info IS 'Database metadata information table';
COMMENT ON COLUMN schema_info.id IS 'Auto-increment primary key';
COMMENT ON COLUMN schema_info.table_name IS 'Table name';
COMMENT ON COLUMN schema_info.table_comment IS 'Table comment. Crucial for NL2SQL accuracy.';
COMMENT ON COLUMN schema_info.column_name IS 'Column name';
COMMENT ON COLUMN schema_info.column_comment IS 'Column comment. Crucial for NL2SQL accuracy.';
COMMENT ON COLUMN schema_info.data_type IS 'Column data type';
COMMENT ON COLUMN schema_info.sample_values IS 'Sample values, separated by commas.';
COMMENT ON COLUMN schema_info.is_primary IS 'Indicates whether the column is a primary key (true/false).';
COMMENT ON COLUMN schema_info.is_foreign IS 'Indicates whether the column is a foreign key (true/false).';
COMMENT ON COLUMN schema_info.ext IS 'Foreign key association information. Format: database_name.table_name.column_name';
COMMENT ON COLUMN schema_info.db_type IS 'The language type of the relational database (such as MySQL, PostgreSQL, StarRocks, or Oracle).';

Step 5: Insert metadata

Each row in schema_info describes one column of an external table. The fields fall into three categories:

Identity and structure

Semantic context — these fields have the greatest impact on NL2SQL accuracy

Relationships

Example: inserting metadata for `customers` and `orders` tables

-- Insert metadata for the customers table
INSERT INTO schema_info (table_name, table_comment, column_name, column_comment, data_type, sample_values, is_primary, is_foreign, ext, db_type) VALUES
('customers', 'Customer information table', 'id', 'Unique customer identifier', 'INT', '1,2,3', true, false, NULL, 'MySQL'),
('customers', 'Customer information table', 'name', 'Customer name', 'VARCHAR(100)', 'John Doe,Jane Smith,Peter Jones', false, false, NULL, 'MySQL'),
('customers', 'Customer information table', 'email', 'Customer email', 'VARCHAR(100) UNIQUE', 'johndoe@example.com,janesmith@example.com', false, false, NULL, 'MySQL');

-- Insert metadata for the orders table
INSERT INTO schema_info (table_name, table_comment, column_name, column_comment, data_type, sample_values, is_primary, is_foreign, ext, db_type) VALUES
('orders', 'Order information table', 'order_id', 'Unique order identifier', 'INT', '1001,1005,1003', true, false, NULL, 'MySQL'),
('orders', 'Order information table', 'order_date', 'Order date', 'DATE', '2023-01-01,2023-05-06', false, false, NULL, 'MySQL'),
('orders', 'Order information table', 'customer_id', 'Associated customer ID', 'INT', '1,2,3', false, true, 'dbname.customers.id', 'MySQL'),
('orders', 'Order information table', 'total_amount', 'Total order amount', 'DECIMAL(10,2)', '99.99,101.81,250.00', false, false, NULL, 'MySQL');

Step 6: Grant permissions to the AI node account

Grant the AI node's database account access to the schema_info table. In this example, the account is polarai_user.

GRANT ALL PRIVILEGES ON TABLE public."schema_info" TO polarai_user;