DuckDB v1.5.4 正式发布：JSON 处理、Parquet 优化与 ADBC 1.1.0 支持

-- 创建包含嵌套 JSON 的测试表
CREATE TABLE json_data AS
SELECT * FROM read_json_auto('https://raw.githubusercontent.com/duckdb/duckdb/main/data/json/ticket_order.json');

-- 使用通配符路径提取所有订单中的客户名称
SELECT 
    json_keys(data, '$.order.items[*].customer.name') as customer_names
FROM json_data
LIMIT 5;

-- 修复后可正确处理 NULL JSON 键的情况
SELECT 
    data ->> '$.user.profile.*' as profile_fields
FROM json_data
WHERE data IS NOT NULL;

-- 将数组列转换为 JSON 字符串
CREATE TABLE array_test AS
SELECT 
    [1, 2, 3, 4, 5] as numbers,
    ['a', 'b', 'c'] as letters,
    [true, false, true] as booleans;

SELECT 
    array_to_json(numbers) as numbers_json,
    array_to_json(letters) as letters_json,
    array_to_json(booleans) as booleans_json
FROM array_test;

-- 加载空间扩展
LOAD spatial;

-- 读取包含几何数据的 Parquet 文件
-- DuckDB 会自动利用行组的统计信息进行剪枝
SELECT 
    ST_Area(geometry_col) as area,
    ST_Centroid(geometry_col) as centroid
FROM parquet_scan('s3://data/spatial-data/*.parquet')
WHERE ST_Intersects(geometry_col, ST_MakeEnvelope(-180, -90, 180, 90));

-- 查看 Parquet 文件中 VARIANT 列的字节表示
SELECT 
    file_name,
    row_group_id,
    parquet_variant_bytes(variant_column) as variant_bytes
FROM parquet_metadata('s3://data/large-dataset/*.parquet');

# Python 中使用 ADBC 连接 DuckDB
import adbc.dbapi.sqlite as adbc_sqlite
import pyarrow as pa

# 建立连接
with adbc_sqlite.connect(':memory:') as conn:
    with conn.cursor() as cursor:
        # 执行查询
        cursor.execute("SELECT 1 as test")
        result = cursor.fetch_arrow_table()
        print(result)

# 富错误元数据示例
try:
    cursor.execute("INVALID SQL QUERY")
except adbc_dbapi.ProgrammingError as e:
    # ADBC 1.1.0 提供了详细的错误元数据
    print(f"错误码: {e.error_code}")
    print(f"错误详情: {e.message}")
    print(f"状态信息: {e.state}")

-- 创建测试表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    Username VARCHAR,
    Email VARCHAR
);

-- 现在可以正确匹配大小写不一致的列名
INSERT INTO users (id, username, email)
VALUES (1, '[email protected]', 'John Doe')
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;

-- 设置基于内存的行组刷新阈值（单位：字节）
SET write_buffer_row_group_memory_limit = '1GB';

-- DuckDB 将在行组达到 1GB 内存时使用量时自动刷新
COPY (SELECT * FROM large_table) TO 'output.parquet';

# Linux/Mac 快速升级
curl -Ls https://github.com/duckdb/duckdb/releases/download/v1.5.4/duckdb_cli-linux-amd64.zip -o duckdb.zip
unzip -o duckdb.zip
chmod +x duckdb

# Python 升级
pip install --upgrade duckdb

# Docker 使用
docker pull duckdb/duckdb:v1.5.4