hive 包含所有字段的建表语句

drop table if exists sk_test_create_hive_create_all_fields;
create table if not exists sk_test_create_hive_create_all_fields(
`user_id` bigint COMMENT '产品id',
`user_name` string COMMENT '登录名',
`status` TINYINT COMMENT '用户状态 0正常 1冻结',
`user_name_init` INT COMMENT '用户名是否已修改 0 未修改 1 已修改',
`ctime` TIMESTAMP COMMENT '创建时间',
`invalid` SMALLINT COMMENT 'Y:无效,N:有效',
`last_login_time` date COMMENT '最后登录时间',
`size` float COMMENT 'size',
`dou` DOUBLE,
`dec` DECIMAL,
`success` BOOLEAN,
`file` BINARY,
`arr` array<string>,
`map` MAP<STRING,INT>,
`struct` struct<name:STRING, age:INT>

) comment '' partitioned by (ds string comment '') stored as orc;

 

drop table if exists sk_test_create_hive_all_fields;
create table if not exists sk_test_create_hive_all_fields(
`user_id` bigint COMMENT '产品id',
`user_name` string COMMENT '登录名',
`status` TINYINT COMMENT '用户状态 0正常 1冻结',
`user_name_init` INT COMMENT '用户名是否已修改 0 未修改 1 已修改',
`ctime` TIMESTAMP COMMENT '创建时间',
`invalid` SMALLINT COMMENT 'Y:无效,N:有效',
`last_login_time` date COMMENT '最后登录时间',
`size` float COMMENT 'size',
`dou` DOUBLE,
`dec` DECIMAL,
`success` BOOLEAN,
`file` BINARY,
`arr` array < string >,
`map` MAP < STRING,INT >
) comment '' partitioned by (ds string comment '') row format delimited
fields terminated by "," collection items terminated by ":" stored as orc;

 

insert into
sk_test_create_hive_all_fields partition(ds = '20200601')
values(1,
'xiaodou',
'1',
1,
unix_timestamp(),
'Y',
to_date('2011-12-08 10:03:01'),
'1.933435',
'2.103283928',
'18274514398103941',
'1',
NULL,
array('beijing','shanghai','guangzhou'),
map('bigdata',100)
);

 

 

drop table if exists sk_test_datax_support_hive2mysql;
create table if not exists sk_test_datax_support_hive2mysql(
`user_id` bigint COMMENT '产品id',
`user_name` string COMMENT '登录名',
`status` TINYINT COMMENT '用户状态 0正常 1冻结',
`user_name_init` INT COMMENT '用户名是否已修改 0 未修改 1 已修改',
`ctime` TIMESTAMP COMMENT '创建时间',
`invalid` SMALLINT COMMENT 'Y:无效,N:有效',
`last_login_time` date COMMENT '最后登录时间',
`size` float COMMENT 'size',
`dou` DOUBLE,
`dec` DECIMAL(30,10),
`success` BOOLEAN,
`file` BINARY
) comment '' partitioned by (ds string comment '') row format delimited
fields terminated by "," stored as orc;

 

insert into
sk_test_datax_support_hive2mysql partition(ds = '20200601')
values(1,
'xiaodou',
'1',
1,
unix_timestamp(),
'1',
to_date('2011-12-08 10:03:01'),
'1.933435',
'2.103283928',
'1827451439810',
'1',
unbase64('hello binary !!!')
);