创建分区表：

CREATE TABLE yii_stock_day_all(
    id string,
    close float,
    code string,
    date_str string,
    date_int int,
    high float,
    liquidAssets double,
    low float,
    ma10 float,
    ma20 float,
    ma30 float,
    ma5 float,
    open float,
    p_change float,
    p_volume double,
    pre_close float,
    price_change float,
    totalAssets double,
    turnover float,
    v_ma10 double,
    v_ma20 double,
    v_ma30 double,
    v_ma5 double,
    volume double
)
partitioned by (ym int)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
STORED AS TEXTFILE;

查询写入动态分区：

前置设置：

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;

插入动态分区 SQL:

INSERT OVERWRITE TABLE yii_stock_day_all
PARTITION (ym)
SELECT id,close,code, date_format(from_unixtime(UNIX_TIMESTAMP(date_str, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")), 'yyyy-MM-dd HH:mm:ss') as date_str, date_int,
high, liquidAssets, low,ma10, ma20,ma30,
ma5,open, p_change, p_volume,pre_close, price_change, 
totalAssets,turnover, v_ma10,
v_ma20, v_ma30, v_ma5, volume,
cast(date_format(from_unixtime(UNIX_TIMESTAMP(cast(date_int as string), 'yyyyMMdd')), 'yyyyMM') as int) as ym
FROM yii_stock_day where volume > 0;

增量数据插入动态分区：

INSERT INTO yii_stock_day_all
PARTITION (ym)
SELECT id,close,code, date_str, date_int,
high, liquidAssets, low,ma10, ma20,ma30,
ma5,open, p_change, p_volume,pre_close, price_change, 
totalAssets,turnover, vma10 as v_ma10,
vma20 as v_ma20, vma30 as v_ma30, vma5 as v_ma5, volume,
cast(date_format(from_unixtime(UNIX_TIMESTAMP(cast(date_int as string), 'yyyyMMdd')), 'yyyyMM') as int) as ym
FROM yii_stock_fetch_bulk;

date 类型练习:

select code,date_int,turnover,to_date(from_unixtime(UNIX_TIMESTAMP(cast(date_int as string), 'yyyyMMdd'))) 
from yii_stock_day limit 100;

select code,date_int,turnover, date_format(cast(date_int as string), 'yyyyMMdd')  
from yii_stock_day limit 100;