本文共 2049 字,大约阅读时间需要 6 分钟。
CREATE EXTERNAL TABLE [dbname.]表名( rowkey string, name string) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping"= ":key, f:name") TBLPROPERTIES ("hbase.table.name" = "hbase表");
create table users(id int,user_id int,user_name string);create table product(id int,product_id int,product_name string,price double);create table orders (id int,user_id int,product_id int,product_count int) partitioned by (year string,month string,day string);--------------指定分区:插入数据insert into users values(1,1,'a'),(2,2,'b'),(3,3,'c');insert into product values(1,1,'iphone6',9000),(2,2,'<>',23.2),(3,3,'lenovo-pc',5623.4);insert into orders partition(year='2019',month='03',day='02') values(1,1,1,2),(2,1,3,1),(3,2,2,3);insert into orders partition(year='2019',month='03',day='03') values(null,1,2,1),(null,2,1,4);------------动态分区:插入数据:create table orders_t1 (id int,user_id int,product_id int,product_count int,time string) ;insert into orders_t1 values(1,1,1,2,'2011-12-02 12:03:32'),(2,1,3,1,'2014-12-05 16:06:32'),(3,2,2,3,'2016-10-02 18:03:32');set hive.exec.dynamic.partition.mode=nonstrict;create table orders2 like orders;insert into orders2 partition(year,month,day)select id,user_id,product_id,product_count,year(time),month(time),day(time) from orders_t1;--====================查询语句--解决错误:Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask (state=08S01,code=1)SET hive.exec.mode.local.auto=true;SET hive.auto.convert.join=false;SET mapreduce.map.memory.mb = 26384; SET mapreduce.map.java.opts='-Djava.net.preferIPv4Stack=true -Xmx23107M';SET mapreduce.reduce.memory.mb = 26384; SET mapreduce.reduce.java.opts='-Djava.net.preferIPv4Stack=true -Xmx23107M';set hive.support.concurrency = false;select user_name, product_name, product_count from ordersleft join product on product.product_id=orders.product_idleft join users on users.user_id=orders.user_id
转载地址:http://zhdef.baihongyu.com/