Hive和连接数据库命令总结

一、Hive命令总结

从文件插入数据 load data local inpath '/home/chris/test.dat' into table crm_sal_shop_sale_testdata;

下载数据 insert overwrite local directory '/home/chris' row format delimited fields terminated by ',' select * from database.table;

              hive -e 'set hive.cli.print.header=true; select * from database.table' > /home/chris/test

复制表结构： create table new_table as select * from exists_table where 1=0;

复制表结构和数据： create table new_table as select * from exists_table;

从表插入数据 insert into table t_temp select * from t_p;

删除表 drop table product;

删除部分数据  insert overwrite table tablename partition(dt=x) select a,b from tablename where dt=x;

清除表数据:truncate table 表名;

表改名 ALTER TABLE a RENAME TO b;

分区改名 ALTER TABLE table_name PARTITION (dt='x') RENAME TO PARTITION (dt='y');

建立数据库 CREATE SCHEMA userdb;

删除数据库  drop database recommand cascade;

显示列名 set hive.cli.print.header=true;

创建表：create table itemcf (vipid string,recommand string,rank int);

改列名：ALTER TABLE itemcf CHANGE id vipid string;

杀死进程 yarn application -kill application_1561725396351_0198

二、连接数据库

连接sqlserver

import pymssql

conn=pymssql.connect(host='192.168.12.118',user='sa',password='123456',database="recommend")

cur = conn.cursor()

连接mysql

import pymysql

conn=pymysql.connect(host='192.168.12.118',user='root',password='123456',database="recommend")

cur=conn.cursor()

cur.execute("select * from product ")

data=cur.fetchall()

conn.close()

另外可参考文档：https://blog.csdn.net/qq_38803950/article/details/86005993