ArgoDB SQL Skill

This skill provides comprehensive SQL guidance for Transwarp ArgoDB 5.2.1, a distributed analytical database developed by Transwarp.

Skill Purpose

Generate SQL statements for ArgoDB based on user requirements. The skill provides:

• DDL (Data Definition Language) guidance for database/table creation
• DML (Data Manipulation Language) for data operations
• DQL (Data Query Language) for SELECT queries
• Best practices and syntax references

Important: This skill only generates SQL statements for user execution. Do NOT connect to ArgoDB databases.

Usage Guidelines

When Triggered

Use this skill when user mentions:

• "write SQL for ArgoDB"
• "create table/query data in ArgoDB"
• "ArgoDB SQL syntax"
• Any SQL-related task for Transwarp database

SQL Generation Workflow

1. Understand Requirements: Identify the operation type (DDL/DML/DQL)
2. Check References: Load relevant reference documents from references/
3. Apply Standards: Follow type specifications and field requirements
4. Generate SQL: Use appropriate syntax patterns from references
5. Validate: Ensure SQL follows ArgoDB 5.2.1 syntax rules

Reference Documents

| Document | Content | When to Load |

|----------|---------|--------------|

| references/ddl_syntax.md | CREATE DATABASE/TABLE, ALTER, DROP | Table creation, schema changes |

| references/dml_syntax.md | INSERT, UPDATE, DELETE, MERGE | Data manipulation |

| references/dql_syntax.md | SELECT, JOIN, UNION | Query operations |

| references/data_types.md | Supported data types | Column type selection |

| references/storage_formats.md | STORED AS options | Storage format selection |

Type Specifications (Mandatory)

> Important: Follow these specifications for all SQL generation.

Standard Type Mapping

| Data Type | Use Case | Example |

|-----------|----------|---------|

| string | All strings | Names, codes, descriptions |

| bigint | All integers | IDs, quantities |

| decimal(38,10) | All decimals | Prices, amounts, rates |

| date | Date | YYYY-MM-DD |

| timestamp | Timestamp | YYYY-MM-DD HH:MI:SS |

Do NOT Use

• ~~VARCHAR(n)~~ → Use string
• ~~CHAR(n)~~ → Use string
• ~~INT~~ → Use bigint
• ~~DECIMAL(p,s)~~ → Use decimal(38,10)
• ~~TEXT~~ → Use string

Table Creation Standards (Mandatory)

Required Elements

1. Table Comment: Every table MUST have a COMMENT
2. Field Comments: Every field MUST have a COMMENT
3. Standard Technical Fields: Every table MUST include these 5 fields

Standard Technical Fields

| Field Name | Type | Comment | Description |

|------------|------|---------|--------------|

| src_tab_nm | string | '来源表名' | Source table name |

| etl_job | string | 'ETL加载作业名' | ETL job name |

| etl_tx_dt | date | 'ETL加载日期' | ETL transaction date |

| etl_upd_dt | date | 'ETL更新日期' | ETL update date |

| etl_proc_dttm | timestamp | 'ETL处理时间戳' | ETL process timestamp |

Example: Correct Table Structure

CREATE TABLE sales (
    -- Business fields
    trans_id bigint COMMENT '交易ID',
    trans_time date COMMENT '交易时间',
    trans_type string COMMENT '交易类型',
    amount decimal(38,10) COMMENT '交易金额',
    -- Standard technical fields
    src_tab_nm string COMMENT '来源表名',
    etl_job string COMMENT 'ETL加载作业名',
    etl_tx_dt date COMMENT 'ETL加载日期',
    etl_upd_dt date COMMENT 'ETL更新日期',
    etl_proc_dttm timestamp COMMENT 'ETL处理时间戳'
)
COMMENT '销售交易表'
STORED AS HOLODESK;

Example: Incorrect Table Structure

-- WRONG: Missing comments
CREATE TABLE sales (
    trans_id INT,          -- Missing COMMENT
    amount DECIMAL(10,2),  -- Missing COMMENT
    trans_type VARCHAR(50) -- Wrong type, Missing COMMENT
);

-- WRONG: Missing technical fields
CREATE TABLE sales (
    trans_id bigint COMMENT '交易ID',
    amount decimal(38,10) COMMENT '交易金额'
    -- Missing src_tab_nm, etl_job, etl_tx_dt, etl_upd_dt, etl_proc_dttm
);

Storage Formats (STORED AS)

| Format | Description | Use Case |

|--------|-------------|----------|

| HOLODESK | High-performance columnar storage (recommended) | Big data analytics |

| TEXTFILE | Text file format | Default format |

| ORC | Optimized Row Columnar | Hive compatibility |

| PARQUET | Columnar format | Cross-platform |

| TORC | Transactional ORC | ACID transactions |

Recommendation: Use HOLODESK as default storage format.

Key Syntax Highlights

Supported Dialects

• Oracle (default)
• IBM Db2
• Teradata

Set dialect: SET server.dialect = ;

SQL Execution Order

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Examples

Create Holodesk Table

CREATE TABLE sales (
    trans_id bigint COMMENT '交易ID',
    acc_num bigint COMMENT '账户号',
    trans_time date COMMENT '交易时间',
    trans_type string COMMENT '交易类型',
    price decimal(38,10) COMMENT '价格',
    amount bigint COMMENT '数量',
    -- Standard technical fields
    src_tab_nm string COMMENT '来源表名',
    etl_job string COMMENT 'ETL加载作业名',
    etl_tx_dt date COMMENT 'ETL加载日期',
    etl_upd_dt date COMMENT 'ETL更新日期',
    etl_proc_dttm timestamp COMMENT 'ETL处理时间戳'
)
COMMENT '销售交易信息表'
STORED AS HOLODESK;

Insert Data

-- Single row
INSERT INTO sales VALUES (
    1, 100, '2024-01-01', 'online', 99.99, 5,
    'source_table', 'job_sales', '2024-01-01', '2024-01-01', '2024-01-01 14:30:00'
);

-- Batch insert
INSERT INTO sales VALUES
    (2, 101, '2024-01-02', 'store', 49.99, 10, 'source_table', 'job_sales', '2024-01-02', '2024-01-02', '2024-01-02 14:30:00'),
    (3, 102, '2024-01-03', 'wholesale', 199.99, 2, 'source_table', 'job_sales', '2024-01-03', '2024-01-03', '2024-01-03 14:30:00');

Query with Join

SELECT
    e.name,
    d.dept_name,
    e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.is_valid = 'Y'
ORDER BY e.salary DESC
LIMIT 10;

Safety Rules

1. Never connect to database: Only generate SQL statements
2. User executes: All SQL must be provided to user for execution
3. Validate syntax: Ensure compliance with ArgoDB 5.2.1 specifications
4. Follow standards: Always use specified types and include required fields