Snowflake 表格函式深度解析：半結構化資料處理的實戰指南

SELECT
    value:empid::NUMBER AS employee_id,
    value:name::VARCHAR AS employee_name,
    value:department::VARCHAR AS department,
    value:salary::NUMBER AS salary
FROM
    TABLE(FLATTEN(INPUT => PARSE_JSON(
        '{
            "employees": [
                {
                    "empid": 1001, 
                    "name": "陳大明",
                    "department": "工程部",
                    "salary": 85000
                },
                {
                    "empid": 1002, 
                    "name": "林小華",
                    "department": "行銷部",
                    "salary": 72000
                },
                {
                    "empid": 1003, 
                    "name": "王志明",
                    "department": "財務部",
                    "salary": 78000
                },
                {
                    "empid": 1004, 
                    "name": "張美玲",
                    "department": "人資部",
                    "salary": 68000
                },
                {
                    "empid": 1005, 
                    "name": "劉建國",
                    "department": "工程部",
                    "salary": 92000
                }
            ]
        }'
    ), PATH => 'employees'));

SELECT
    query_id,
    query_text,
    database_name,
    schema_name,
    query_type,
    warehouse_name,
    warehouse_size,
    execution_status,
    error_code,
    error_message,
    start_time,
    end_time,
    total_elapsed_time / 1000 AS execution_seconds,
    bytes_scanned,
    bytes_written,
    rows_produced,
    compilation_time,
    execution_time,
    queued_provisioning_time,
    queued_repair_time,
    queued_overload_time
FROM
    TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
        RESULT_LIMIT => 100,
        END_TIME_RANGE_START => DATEADD(hour, -24, CURRENT_TIMESTAMP())
    ))
WHERE
    execution_status = 'SUCCESS'
    AND query_type IN ('SELECT', 'INSERT', 'UPDATE')
ORDER BY
    total_elapsed_time DESC;

-- 首先找出特定查詢的 ID
SELECT
    query_id,
    SUBSTR(query_text, 1, 50) AS partial_query,
    start_time,
    total_elapsed_time / 1000 AS seconds
FROM
    TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
        RESULT_LIMIT => 20
    ))
WHERE
    query_text ILIKE '%SELECT * FROM sales_summary%'
ORDER BY
    start_time DESC;

-- 使用找到的 QUERY_ID 檢索結果
SELECT *
FROM TABLE(RESULT_SCAN('01b23456-789a-bcde-f012-3456789abcde'))
LIMIT 100;

-- 建立包含 VARIANT 欄位的表格
CREATE TABLE product_catalog (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR(200),
    category VARCHAR(100),
    base_price NUMBER(10,2),
    attributes VARIANT,
    created_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

-- 插入包含不同結構的產品資料
INSERT INTO product_catalog (product_id, product_name, category, base_price, attributes)
VALUES
    (1001, '智慧型手機 Pro', '電子產品', 25000,
     PARSE_JSON('{
         "brand": "TechCorp",
         "model": "X-2024",
         "specs": {
             "screen_size": "6.7吋",
             "ram": "12GB",
             "storage": "256GB",
             "camera": "108MP"
         },
         "colors": ["黑色", "銀色", "金色"]
     }')),
    (1002, '無線耳機 Elite', '電子產品', 4500,
     PARSE_JSON('{
         "brand": "AudioMax",
         "model": "Elite-500",
         "specs": {
             "battery_life": "30小時",
             "noise_cancellation": true,
             "bluetooth_version": "5.3"
         },
         "colors": ["黑色", "白色"]
     }')),
    (1003, '運動智慧手錶', '穿戴裝置', 8900,
     PARSE_JSON('{
         "brand": "FitTrack",
         "model": "Sport-X",
         "specs": {
             "display": "AMOLED",
             "waterproof": "5ATM",
             "heart_rate_monitor": true,
             "gps": true
         },
         "colors": ["黑色", "藍色", "紅色"],
         "compatible_os": ["iOS", "Android"]
     }'));

-- 查詢 VARIANT 欄位中的資料
SELECT
    product_id,
    product_name,
    attributes:brand::VARCHAR AS brand,
    attributes:specs:ram::VARCHAR AS ram,
    attributes:specs:storage::VARCHAR AS storage
FROM
    product_catalog
WHERE
    category = '電子產品'
    AND attributes:specs:ram IS NOT NULL;

-- 展開 VARIANT 中的陣列
SELECT
    p.product_id,
    p.product_name,
    f.value::VARCHAR AS available_color
FROM
    product_catalog p,
    LATERAL FLATTEN(INPUT => p.attributes:colors) f
WHERE
    p.category = '電子產品';

-- 驗證 JSON 格式並安全載入
CREATE TABLE json_import_staging (
    raw_data VARCHAR,
    parsed_data VARIANT,
    is_valid BOOLEAN,
    load_timestamp TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

INSERT INTO json_import_staging (raw_data, parsed_data, is_valid)
SELECT
    raw_json,
    TRY_PARSE_JSON(raw_json) AS parsed,
    TRY_PARSE_JSON(raw_json) IS NOT NULL AS valid
FROM
    external_json_source;

-- 分析無效的 JSON 記錄
SELECT
    raw_data,
    load_timestamp
FROM
    json_import_staging
WHERE
    is_valid = FALSE;

-- 處理有效的記錄
INSERT INTO product_catalog (product_id, product_name, category, base_price, attributes)
SELECT
    parsed_data:id::NUMBER,
    parsed_data:name::VARCHAR,
    parsed_data:category::VARCHAR,
    parsed_data:price::NUMBER,
    parsed_data:attributes
FROM
    json_import_staging
WHERE
    is_valid = TRUE;

-- 建立包含階層結構的組織資料
CREATE TABLE organization_hierarchy (
    org_id NUMBER PRIMARY KEY,
    org_data VARIANT
);

INSERT INTO organization_hierarchy VALUES
(1, PARSE_JSON('{
    "company": "科技創新股份有限公司",
    "departments": [
        {
            "dept_id": "D001",
            "dept_name": "研發部",
            "manager": "張志明",
            "teams": [
                {
                    "team_id": "T001",
                    "team_name": "前端團隊",
                    "members": [
                        {"emp_id": "E001", "name": "陳小華", "position": "資深工程師"},
                        {"emp_id": "E002", "name": "林美玲", "position": "工程師"}
                    ]
                },
                {
                    "team_id": "T002",
                    "team_name": "後端團隊",
                    "members": [
                        {"emp_id": "E003", "name": "王大明", "position": "架構師"},
                        {"emp_id": "E004", "name": "李小芬", "position": "資深工程師"}
                    ]
                }
            ]
        },
        {
            "dept_id": "D002",
            "dept_name": "行銷部",
            "manager": "劉建國",
            "teams": [
                {
                    "team_id": "T003",
                    "team_name": "數位行銷",
                    "members": [
                        {"emp_id": "E005", "name": "周雅婷", "position": "行銷經理"},
                        {"emp_id": "E006", "name": "吳志豪", "position": "行銷專員"}
                    ]
                }
            ]
        }
    ]
}'));

-- 多層展開取得所有員工資訊
WITH departments AS (
    SELECT
        org_id,
        dept.value:dept_id::VARCHAR AS dept_id,
        dept.value:dept_name::VARCHAR AS dept_name,
        dept.value:manager::VARCHAR AS manager,
        dept.value:teams AS teams
    FROM
        organization_hierarchy,
        LATERAL FLATTEN(INPUT => org_data:departments) dept
),
teams AS (
    SELECT
        d.org_id,
        d.dept_id,
        d.dept_name,
        d.manager,
        team.value:team_id::VARCHAR AS team_id,
        team.value:team_name::VARCHAR AS team_name,
        team.value:members AS members
    FROM
        departments d,
        LATERAL FLATTEN(INPUT => d.teams) team
),
employees AS (
    SELECT
        t.org_id,
        t.dept_id,
        t.dept_name,
        t.manager AS dept_manager,
        t.team_id,
        t.team_name,
        member.value:emp_id::VARCHAR AS emp_id,
        member.value:name::VARCHAR AS emp_name,
        member.value:position::VARCHAR AS position
    FROM
        teams t,
        LATERAL FLATTEN(INPUT => t.members) member
)
SELECT
    dept_name AS 部門,
    team_name AS 團隊,
    emp_name AS 員工姓名,
    position AS 職位,
    dept_manager AS 部門主管
FROM
    employees
ORDER BY
    dept_id, team_id, emp_id;

-- 建立視圖封裝 flatten 邏輯
CREATE VIEW employee_details_view AS
WITH departments AS (
    SELECT
        org_id,
        dept.value:dept_id::VARCHAR AS dept_id,
        dept.value:dept_name::VARCHAR AS dept_name,
        dept.value:manager::VARCHAR AS manager,
        dept.value:teams AS teams
    FROM
        organization_hierarchy,
        LATERAL FLATTEN(INPUT => org_data:departments) dept
),
teams AS (
    SELECT
        d.org_id,
        d.dept_id,
        d.dept_name,
        d.manager,
        team.value:team_id::VARCHAR AS team_id,
        team.value:team_name::VARCHAR AS team_name,
        team.value:members AS members
    FROM
        departments d,
        LATERAL FLATTEN(INPUT => d.teams) team
)
SELECT
    t.org_id,
    t.dept_id,
    t.dept_name,
    t.manager AS dept_manager,
    t.team_id,
    t.team_name,
    member.value:emp_id::VARCHAR AS emp_id,
    member.value:name::VARCHAR AS emp_name,
    member.value:position::VARCHAR AS position
FROM
    teams t,
    LATERAL FLATTEN(INPUT => t.members) member;

-- 使用視圖進行簡單查詢
SELECT
    dept_name,
    COUNT(*) AS employee_count
FROM
    employee_details_view
GROUP BY
    dept_name;

-- 資料品質檢查與驗證
SELECT
    org_id,
    org_data,
    CASE
        WHEN org_data:company IS NULL THEN '缺少公司名稱'
        WHEN org_data:departments IS NULL THEN '缺少部門資料'
        WHEN TYPEOF(org_data:departments) != 'ARRAY' THEN '部門資料格式錯誤'
        WHEN ARRAY_SIZE(org_data:departments) = 0 THEN '部門陣列為空'
        ELSE '資料正常'
    END AS data_quality_check
FROM
    organization_hierarchy;