Hive 语句

一、 修改hosts文件：
在hosts文件末尾加上
0.0.0.0 account.jetbrains.com
0.0.0.0 www.jetbrains.com

$>spark-sql --queue=dev --num-executors 10 --executor-memory 10G
$>show databases;
$> show tables
$>select * from dwm.cn_tl_base limit 1;

$>spark-sql --queue=dev --num-executors 10 --executor-memory 10G --hiveconf hive.cli.print.header=true
$>beeline -u jdbc:hive2://localhost:10005 -n liyingying

//导入数据
//origion
sqoop import -D mapred.job.queue.name=prod
--connect 'jdbc:sqlserver://10.1.2.55:1433;database=docdbfamily'
--username 'sa'
--password 'password123456*'
--query 'select id,fid from fid_pn with(nolock) where $CONDITIONS'
--hive-database 'pro'
--hive-import
--hive-table 'fid_pn'
--target-dir /user/hive/warehouse/pro.db/fid_pn
--as-parquetfile
--split-by id
-m 10;

-m(10) 分多少进程跑

//sucess
sqoop import -D mapred.job.queue.name=prod
--connect 'jdbc:sqlserver://10.1.2.58:1433;database=law'
--username 'sa'
--password 'password123456*'
--query 'select id,pnk,pd,new_pd,law,lawdetail,cdate from law_lyy A with(nolock) where $CONDITIONS '
--hive-database 'lyytest'
--hive-import
--hive-table 'law_test'
--split-by 'id'
--target-dir /user/hive/warehouse/lyytest.db/law_test;

sqoop import -D mapred.job.queue.name=prod
--connect 'jdbc:sqlserver://10.1.2.58:1433;database=CN_XUKE_ZR'
--username 'sa'
--password 'password123456*'
--query 'select id,pnk from View_law_test0813 A with(nolock) where $CONDITIONS '
--hive-database 'dwm'
--hive-import
--hive-table 'law_all'
--split-by 'id'
--target-dir /user/hive/warehouse/dwm.db/law_all;

//挂载文件：

创建一个本地文件夹
>>mkdir mnt58_CNabxml
将共享文件夹挂载到新创建的文件夹
>>mount -t cifs -o username=administrator,password=123@abc //10.1.2.58/ln_cn_abxml /mnt58_CNabxml

create table if not exists dwm.law_all (id string ,pnk string)
select A.insert_date,A.pn,A.pd,A.law,A.law_detail from dwm.view_pn_lawtest A,dwm.law_all B where A.pn=B.pnk order by A.pn,A.pd limit 10;

//杀死自己的任务
$>quit;
$>yarn top
$>yarn application -kill +进程

创建表 导入数据
//low
insert into table dwm.pn_law_pn select pn,pd,law,law_detail from dwm.view_pn_law limit 1000;
create table if not exists dwm.pn_law_pn (pn string,pd string,law string,law_detail string)

//assignee
insert into table dwm.pn_assignl_pn select pn,an,pd,law,law_detail,assignor,djsxr,assignee,change_type,common_assignor,common_assignee,assignor_addr,assignee_addr,insert_date,update_date from dwm.view_pn_assignl where pn='CN101806533A';

//创建带分区的表
CREATE TABLE `dim`.`pn_cn_law_lyy`(`pn` string, `an` string, `pd` string, `lpd` string, `law` string, `law_pd` string, `law_code` string, `law_detail` string, `statutype` string, `simptype` string, `expiry_date` string, `insert_date` string, `expiry_dc` string)
PARTITIONED BY (`p_d` string) stored as parquet;

//删除数据
DROP TABLE IF EXISTS dwm.pn_assignl_pn ;
//统计数据
select count(*) from dwm.view_pn_assignl;
//三表关联找pn
select u.pn from dwm.view_pn_law r join dwm.view_pn_assignl u on r.pn = u.pn join dwm.view_pn_License m on r.pn = m.pn;

//查看分区
show partitions ods.ln_zlsj;