Cassandra数据类型 #

Cassandra数据类型
├── 基本类型
│   ├── 数值类型
│   ├── 文本类型
│   ├── 时间类型
│   └── 其他类型
├── 集合类型
│   ├── List
│   ├── Set
│   └── Map
├── 用户定义类型
│   └── UDT
└── 特殊类型
    ├── Counter
    ├── Tuple
    └── Frozen

-- 创建包含数值类型的表
CREATE TABLE numeric_example (
    id UUID PRIMARY KEY,
    tiny_val TINYINT,
    small_val SMALLINT,
    int_val INT,
    big_val BIGINT,
    var_val VARINT,
    float_val FLOAT,
    double_val DOUBLE,
    decimal_val DECIMAL
);

-- 插入数据
INSERT INTO numeric_example (
    id, tiny_val, small_val, int_val, big_val,
    var_val, float_val, double_val, decimal_val
) VALUES (
    uuid(), 100, 30000, 2000000000, 9000000000000000000,
    123456789012345678901234567890, 3.14, 3.14159265358979, 3.14159265358979323846
);

-- 创建包含文本类型的表
CREATE TABLE text_example (
    id UUID PRIMARY KEY,
    name TEXT,
    description VARCHAR,
    code ASCII
);

-- 插入数据
INSERT INTO text_example (id, name, description, code)
VALUES (
    uuid(), 
    '张三', 
    '这是一个很长的描述文本...',
    'ABC123'
);

-- 字符串函数
SELECT 
    name,
    length(name) as name_length,
    lower(name) as name_lower,
    upper(name) as name_upper
FROM text_example;

-- 创建包含时间类型的表
CREATE TABLE time_example (
    id UUID PRIMARY KEY,
    created_at TIMESTAMP,
    birth_date DATE,
    work_start TIME,
    duration_val DURATION
);

-- 插入数据
INSERT INTO time_example (id, created_at, birth_date, work_start, duration_val)
VALUES (
    uuid(),
    toTimestamp(now()),
    '1990-05-20',
    '09:00:00.000',
    1y2mo3d4h5m6s
);

-- 时间函数
SELECT 
    created_at,
    toDate(created_at) as date_part,
    toTime(created_at) as time_part,
    toUnixTimestamp(created_at) as unix_ts
FROM time_example;

-- 创建包含其他类型的表
CREATE TABLE misc_example (
    id UUID PRIMARY KEY,
    is_active BOOLEAN,
    user_uuid UUID,
    time_uuid TIMEUUID,
    ip_address INET,
    binary_data BLOB
);

-- 插入数据
INSERT INTO misc_example (
    id, is_active, user_uuid, time_uuid, ip_address, binary_data
) VALUES (
    uuid(),
    true,
    uuid(),
    now(),
    '192.168.1.100',
    0x48656c6c6f20576f726c64
);

-- UUID函数
SELECT 
    id,
    user_uuid,
    time_uuid,
    dateOf(time_uuid) as uuid_date,
    unixTimestampOf(time_uuid) as uuid_ts
FROM misc_example;

-- 创建包含List的表
CREATE TABLE list_example (
    user_id UUID PRIMARY KEY,
    name TEXT,
    tags LIST<TEXT>,
    scores LIST<INT>
);

-- 插入数据
INSERT INTO list_example (user_id, name, tags, scores)
VALUES (uuid(), '张三', ['vip', 'active', 'premium'], [85, 90, 78]);

-- 追加元素
UPDATE list_example 
SET tags = tags + ['new_tag']
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 前置添加元素
UPDATE list_example 
SET tags = ['first'] + tags
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 替换特定位置元素（索引从0开始）
UPDATE list_example 
SET tags[0] = 'updated_tag'
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 删除特定元素
UPDATE list_example 
SET tags = tags - ['vip']
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 查询
SELECT user_id, tags, tags[0] as first_tag FROM list_example;

-- 创建包含Set的表
CREATE TABLE set_example (
    user_id UUID PRIMARY KEY,
    name TEXT,
    emails SET<TEXT>,
    phone_numbers SET<TEXT>
);

-- 插入数据
INSERT INTO set_example (user_id, name, emails, phone_numbers)
VALUES (uuid(), '李四', {'li@example.com', 'si@example.com'}, {'13800138000'});

-- 添加元素
UPDATE set_example 
SET emails = emails + {'new@example.com'}
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 删除元素
UPDATE set_example 
SET emails = emails - {'old@example.com'}
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 查询
SELECT user_id, emails FROM set_example;

-- 创建包含Map的表
CREATE TABLE map_example (
    user_id UUID PRIMARY KEY,
    name TEXT,
    preferences MAP<TEXT, TEXT>,
    scores MAP<TEXT, INT>
);

-- 插入数据
INSERT INTO map_example (user_id, name, preferences, scores)
VALUES (
    uuid(), 
    '王五', 
    {'theme': 'dark', 'language': 'zh', 'timezone': 'Asia/Shanghai'},
    {'math': 90, 'english': 85, 'science': 88}
);

-- 添加键值对
UPDATE map_example 
SET preferences = preferences + {'notification': 'enabled'}
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 更新特定键
UPDATE map_example 
SET preferences['theme'] = 'light'
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 删除键
DELETE preferences['language'] 
FROM map_example 
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 查询特定键值
SELECT user_id, preferences['theme'] as theme FROM map_example;

-- 查询所有键或值
SELECT user_id, keys(preferences), values(preferences) FROM map_example;

-- 集合类型有以下限制：

-- 1. 单个集合项最大64KB
-- 2. 集合不适合存储大量数据（建议不超过1000个元素）
-- 3. 查询集合内部需要读取整个集合

-- 不推荐：大量数据的集合
-- CREATE TABLE bad_example (
--     user_id UUID PRIMARY KEY,
--     all_history LIST<TEXT>  -- 不推荐存储大量历史记录
-- );

-- 推荐：使用分区表存储大量数据
CREATE TABLE user_history (
    user_id UUID,
    event_time TIMESTAMP,
    event_data TEXT,
    PRIMARY KEY (user_id, event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

-- 创建地址类型
CREATE TYPE address (
    street TEXT,
    city TEXT,
    state TEXT,
    zip_code TEXT,
    country TEXT
);

-- 创建联系方式类型
CREATE TYPE contact (
    phone TEXT,
    email TEXT,
    wechat TEXT
);

-- 创建嵌套UDT
CREATE TYPE full_address (
    location address,
    contact contact,
    is_primary BOOLEAN
);

-- 创建使用UDT的表
CREATE TABLE udt_example (
    user_id UUID PRIMARY KEY,
    name TEXT,
    home_address FROZEN<address>,
    work_address FROZEN<address>,
    full_info FROZEN<full_address>
);

-- 插入数据
INSERT INTO udt_example (user_id, name, home_address)
VALUES (
    uuid(),
    '赵六',
    {street: '朝阳路100号', city: '北京', state: '北京', zip_code: '100000', country: '中国'}
);

-- 更新UDT字段
UPDATE udt_example 
SET home_address = {
    street: '海淀路200号',
    city: '北京',
    state: '北京',
    zip_code: '100080',
    country: '中国'
}
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 更新UDT中的单个字段（需要整体更新）
UPDATE udt_example 
SET home_address.city = '上海'
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 查询
SELECT user_id, name, home_address.city FROM udt_example;

-- 创建包含UDT集合的表
CREATE TABLE udt_collection (
    user_id UUID PRIMARY KEY,
    name TEXT,
    addresses LIST<FROZEN<address>>,
    contacts MAP<TEXT, FROZEN<contact>>
);

-- 插入数据
INSERT INTO udt_collection (user_id, name, addresses, contacts)
VALUES (
    uuid(),
    '钱七',
    [
        {street: '地址1', city: '北京', state: '北京', zip_code: '100000', country: '中国'},
        {street: '地址2', city: '上海', state: '上海', zip_code: '200000', country: '中国'}
    ],
    {
        'home': {phone: '13800138000', email: 'home@example.com', wechat: 'wx_home'},
        'work': {phone: '13900139000', email: 'work@example.com', wechat: 'wx_work'}
    }
);

-- 创建计数器表（只能包含主键和计数器列）
CREATE TABLE counter_example (
    user_id UUID PRIMARY KEY,
    login_count COUNTER,
    page_views COUNTER,
    purchase_count COUNTER
);

-- 增加计数
UPDATE counter_example 
SET login_count = login_count + 1,
    page_views = page_views + 10,
    purchase_count = purchase_count + 1
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 减少计数
UPDATE counter_example 
SET login_count = login_count - 1
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 查询
SELECT * FROM counter_example WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- 创建包含Tuple的表
CREATE TABLE tuple_example (
    user_id UUID PRIMARY KEY,
    name TEXT,
    coordinates TUPLE<FLOAT, FLOAT>,
    rgb_color TUPLE<INT, INT, INT>,
    info TUPLE<TEXT, INT, BOOLEAN>
);

-- 插入数据
INSERT INTO tuple_example (user_id, name, coordinates, rgb_color, info)
VALUES (
    uuid(),
    '孙八',
    (39.9042, 116.4074),
    (255, 128, 0),
    ('描述信息', 100, true)
);

-- 查询
SELECT user_id, coordinates, rgb_color FROM tuple_example;

-- 访问元组元素（从1开始）
SELECT user_id, coordinates.f1 as latitude, coordinates.f2 as longitude 
FROM tuple_example;

-- Frozen特点：
-- 1. 整体更新，不能部分更新
-- 2. 可以作为主键的一部分
-- 3. 可以用于UDT、集合、元组

-- 作为主键的一部分
CREATE TABLE frozen_pk_example (
    user_id UUID,
    address FROZEN<address>,
    created_at TIMESTAMP,
    PRIMARY KEY (user_id, address)
);

-- Frozen集合
CREATE TABLE frozen_collection (
    user_id UUID PRIMARY KEY,
    tags FROZEN<SET<TEXT>>,
    attributes FROZEN<MAP<TEXT, TEXT>>
);

-- CAST函数
SELECT 
    CAST(age AS BIGINT) as age_bigint,
    CAST(price AS TEXT) as price_text,
    CAST(score AS DOUBLE) as score_double
FROM products;

-- 类型转换函数
SELECT 
    blobAsText(textAsBlob('hello')) as text_back,
    uuid() as new_uuid,
    toDate(now()) as today
FROM system.local;

-- 从JSON创建数据
INSERT INTO users JSON '{
    "user_id": "550e8400-e29b-41d4-a716-446655440000",
    "name": "JSON用户",
    "age": 30,
    "tags": ["tag1", "tag2"],
    "preferences": {"theme": "dark"}
}';

-- 查询结果转JSON
SELECT toJson(user_id), toJson(name), toJson(tags) FROM users;

-- 完整行转JSON
SELECT toJson(users) FROM users;