Hive dataclass型 and store格式

-- creationpackage含数值class型 表
CREATE TABLE IF NOT EXISTS numeric_types (
    tiny_col TINYINT,
    small_col SMALLINT,
    int_col INT,
    big_col BIGINT,
    float_col FLOAT,
    double_col DOUBLE,
    decimal_col DECIMAL(10,2)
);

-- 插入data
INSERT INTO numeric_types VALUES
(100, 20000, 3000000, 4000000000L, 3.14f, 3.1415926, 12345.67);

-- querydata
SELECT * FROM numeric_types;

-- creationpackage含stringclass型 表
CREATE TABLE IF NOT EXISTS string_types (
    string_col STRING,
    varchar_col VARCHAR(50),
    char_col CHAR(10)
);

-- 插入data
INSERT INTO string_types VALUES
('This is a string', 'This is a varchar', 'char fixed');

-- querydata
SELECT * FROM string_types;

-- 查看string long 度
SELECT 
    LENGTH(string_col) AS string_len,
    LENGTH(varchar_col) AS varchar_len,
    LENGTH(char_col) AS char_len
FROM string_types;

-- creationpackage含日期时间class型 表
CREATE TABLE IF NOT EXISTS datetime_types (
    date_col DATE,
    timestamp_col TIMESTAMP
);

-- 插入data
INSERT INTO datetime_types VALUES
('2025-01-15', '2025-01-15 14:30:45.123456789');

-- usingfunction插入当 before 日期 and 时间
INSERT INTO datetime_types VALUES
(CURRENT_DATE(), CURRENT_TIMESTAMP());

-- querydata
SELECT * FROM datetime_types;

-- 日期时间functionusing
SELECT 
    date_col,
    YEAR(date_col) AS year,
    MONTH(date_col) AS month,
    DAY(date_col) AS day,
    timestamp_col,
    HOUR(timestamp_col) AS hour,
    MINUTE(timestamp_col) AS minute,
    SECOND(timestamp_col) AS second
FROM datetime_types;

-- creationpackage含ARRAYclass型 表
CREATE TABLE IF NOT EXISTS array_table (
    id INT,
    name STRING,
    hobbies ARRAY<STRING>
);

-- 插入data
INSERT INTO array_table VALUES
(1, 'Alice', ARRAY('Reading', 'Traveling', 'Photography')),
(2, 'Bob', ARRAY('Sports', 'Music')),
(3, 'Charlie', ARRAY('Cooking', 'Gardening', 'Painting'));

-- queryarray long 度
SELECT id, name, hobbies, SIZE(hobbies) AS hobby_count FROM array_table;

-- 访问array元素 (index from 0开始) 
SELECT id, name, hobbies[0] AS first_hobby, hobbies[1] AS second_hobby FROM array_table;

-- checkarray is 否package含specific元素
SELECT id, name FROM array_table WHERE array_contains(hobbies, 'Reading');

-- unfoldarray
SELECT id, name, hobby
FROM array_table
LATERAL VIEW explode(hobbies) h AS hobby;

-- creationpackage含MAPclass型 表
CREATE TABLE IF NOT EXISTS map_table (
    id INT,
    name STRING,
    scores MAP<STRING, INT>
);

-- 插入data
INSERT INTO map_table VALUES
(1, 'Alice', MAP('Math', 90, 'English', 85, 'Science', 95)),
(2, 'Bob', MAP('Math', 80, 'History', 75)),
(3, 'Charlie', MAP('English', 88, 'Science', 92, 'Art', 90));

-- 访问MAP值
SELECT id, name, scores['Math'] AS math_score, scores['English'] AS english_score FROM map_table;

-- checkMAP is 否package含specific键
SELECT id, name FROM map_table WHERE map_contains_key(scores, 'Math');

-- 获取MAP 键 and 值
SELECT id, name, map_keys(scores) AS subjects, map_values(scores) AS score_values FROM map_table;

-- unfoldMAP
SELECT id, name, subject, score
FROM map_table
LATERAL VIEW explode(scores) s AS subject, score;

-- creationpackage含STRUCTclass型 表
CREATE TABLE IF NOT EXISTS struct_table (
    id INT,
    name STRING,
    address STRUCT<city:STRING, street:STRING, zip:INT>
);

-- 插入data
INSERT INTO struct_table VALUES
(1, 'Alice', NAMED_STRUCT('city', 'Beijing', 'street', 'Main St', 'zip', 100000)),
(2, 'Bob', NAMED_STRUCT('city', 'Shanghai', 'street', 'Second Ave', 'zip', 200000));

-- 访问STRUCT字段
SELECT id, name, address.city, address.street, address.zip FROM struct_table;

-- creationpackage含UNIONTYPEclass型 表
CREATE TABLE IF NOT EXISTS union_table (
    id INT,
    union_col UNIONTYPE<INT, STRING, BOOLEAN>
);

-- 插入data (using不同class型) 
INSERT INTO union_table VALUES
(1, CAST(100 AS UNIONTYPE<INT, STRING, BOOLEAN>)),
(2, CAST('string value' AS UNIONTYPE<INT, STRING, BOOLEAN>)),
(3, CAST(TRUE AS UNIONTYPE<INT, STRING, BOOLEAN>));

-- queryUNIONTYPE值
SELECT id, union_col FROM union_table;

-- 获取UNIONTYPE class型 and 值
SELECT 
    id,
    union_col,
    unión_tag(union_col) AS tag,
    CASE 
        WHEN unión_tag(union_col) = 0 THEN CAST(union_col AS INT)
        WHEN unión_tag(union_col) = 1 THEN CAST(union_col AS STRING)
        WHEN unión_tag(union_col) = 2 THEN CAST(union_col AS BOOLEAN)
    END AS value
FROM union_table;

-- 显式转换example
SELECT 
    CAST('123' AS INT) AS string_to_int,
    CAST(123 AS STRING) AS int_to_string,
    CAST(3.14 AS INT) AS double_to_int,
    CAST('2025-01-15' AS DATE) AS string_to_date,
    CAST(CURRENT_TIMESTAMP() AS DATE) AS timestamp_to_date;

-- creation表时 class型转换
CREATE TABLE IF NOT EXISTS cast_example AS
SELECT 
    id,
    CAST(salary AS DECIMAL(10,2)) AS formatted_salary,
    CAST(hire_date AS STRING) AS hire_date_str
FROM employees;

-- creationusingTextFile格式 表
CREATE TABLE IF NOT EXISTS text_file_table (
    id INT,
    name STRING,
    age INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

-- 加载data
LOAD DATA LOCAL INPATH '/path/to/data.csv' INTO TABLE text_file_table;

-- creationusingORC格式 表
CREATE TABLE IF NOT EXISTS orc_table (
    id INT,
    name STRING,
    age INT,
    department STRING,
    salary DOUBLE
)
STORED AS ORC
TBLPROPERTIES (
    'orc.compress'='SNAPPY',
    'orc.bloom.filter.columns'='id,name',
    'orc.dictionary.key.threshold'='0.8',
    'orc.max.string.length'='256'
);

--  from other表插入data
INSERT INTO orc_table
SELECT * FROM employees;

-- querydata
SELECT * FROM orc_table WHERE department='IT' AND salary > 50000;

-- creationusingParquet格式 表
CREATE TABLE IF NOT EXISTS parquet_table (
    id INT,
    name STRING,
    age INT,
    department STRING,
    salary DOUBLE
)
STORED AS PARQUET
TBLPROPERTIES (
    'parquet.compression'='SNAPPY',
    'parquet.block.size'='256m',
    'parquet.page.size'='1m'
);

--  from other表插入data
INSERT INTO parquet_table
SELECT * FROM employees;

-- querydata
SELECT department, AVG(salary) AS avg_salary
FROM parquet_table
GROUP BY department;

-- 定义Avro schema
CREATE TABLE IF NOT EXISTS avro_table (
    id INT,
    name STRING,
    age INT,
    department STRING
)
STORED AS AVRO
TBLPROPERTIES (
    'avro.schema.url'='hdfs:///path/to/schema.avsc',
    'avro.codec'='snappy'
);

-- 插入data
INSERT INTO avro_table VALUES
(1, 'Alice', 30, 'IT'),
(2, 'Bob', 25, 'HR');

-- 临时设置压缩格式 (session级别) 
SET hive.exec.compress.output=true;
SET mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

-- creation表时指定压缩格式
CREATE TABLE IF NOT EXISTS compressed_table (
    id INT,
    name STRING,
    age INT
)
STORED AS ORC
TBLPROPERTIES (
    'orc.compress'='SNAPPY'
);

-- 插入data时启用压缩
INSERT OVERWRITE TABLE compressed_table
SELECT * FROM source_table;