【CDN+】 一些常用的Linux命令，crontab+VI+Hive（持续更新）

create [external] table [if not exists] table_name (
col_name data_type [comment '字段描述信息']
col_name data_type [comment '字段描述信息'])
[comment '表的描述信息']
[location '指定表的路径']
[partitioned by (col_name data_type,...)]
[clustered by (col_name,col_name,...)]
[sorted by (col_name [asc|desc],...) into num_buckets buckets]
[row format row_format]
[location location_path]

create table student(id int,name string,age int)
partitioned by (sex string)
clustered by(id)
sorted by (age) into 2 buckets
row format delimited fields terminated by ',';

// 查看数据库
show databases;

// 使用数据库
use srm;

// 显示所有的函数
show functions;

// 查看函数用法
describe function substr;

// 查看当前数据库下
show tables;

// 查看表结构
desc invoice_lines;

// 查看某个表的分区情况
show partitions invoice_lines;

// 创建表
CREATE TABLE IF NOT EXISTS srm.invoice_lines_temp2(
SOURCE_SYS_KEY string comment '' ,
LEGAL_COMPANY string comment '' ,
VENDOR_NAME string comment '' ,
INVOICE_UNIT_PRICE double comment '' ,
PREPAY_UNAPPLIED double comment '' ,
GR_NON_VALUATED string comment '' 
)partitioned by(jobid string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
// LOCATION 用于指定表的数据文件路径
# LOCATION 'hdfs://cdh5/tmp/invoice/'; 

// 根据某张表，创建一张机构一样的表
create table invoice_lines_temp2 like invoice_lines;

// 创建外部表
CREATE EXTERNAL TABLE tinvoice_lines(id STRING, name STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/data/test/test_table';

// 删除表，如果是外部表，只会删除元数据(表结构)，不会删除外部文件中
drop table invoice_lines;

// 删除表的某个分区
alter table srm.invoice_lines_temp2 drop partition(jobid='JOBID');

// 删除外部表数据文件以及目录
DFS -rm -r /data/test/test_table;

// 更新表
ALTER TABLE invoice_lines RENAME TO invoice_lines2;
ALTER TABLE invoice_lines ADD COLUMNS (new_col2 INT COMMENT '内容');

// 清空表，比delete快很多，在mysql中会连索引记录都清空。delete会记录日志，truncate 不会记录日志？
truncate table invoice_lines;

// 删除记录
delete from invoice [where xxx = yyy]

-----------------------------------------有关于数据导入------------------------------------------

// 导入本地文件数据到Hive表
load data local inpath '/apps/data/test1.txt'  into table invoice_lines;

// 导入HDFS文件数据到Hive表
load data inpath '/hdfs/app/data/test.txt'  into table invoice_lines;

// 从别的表中查询出相应的数据并导入到Hive表中，注意列数目一定要相同
insert into table invoice_lines select * from invoice_lines_temp2;
// 导入到指定分区表，注意列数目一定要相同
insert into table invoice_lines partition(jobid='106') select xx1,xx2,xx3 from invoice_lines_temp2 where jobid='106';
// 导入到指定分区表，采用动态分区的方式，注意列数目一定要相同
insert into table invoice_lines partition(jobid) select * from invoice_lines_temp2;
// Hive还支持多表插入，即把FROM 写到前面
FROM invoice insert into table invoice_temp1 select xx,xx2 insert into table invoice_temp2 select xx4,xx6;

// 项目上用到的一些写法
INSERT OVERWRITE TABLE srm.invoice_lines_temp2 PARTITION(jobid) SELECT sour_t.* FROM srm.invoice_lines_temp2 sour_t WHERE jobid = '106';
INSERT INTO TABLE srm.invoice_lines SELECT * FROM srm.invoice_lines_temp2 WHERE jobid = '106';
INSERT OVERWRITE TABLE srm.invoice_lines_temp2 PARTITION(jobid) SELECT * FROM srm.invoice_lines_temp2 WHERE jobid='106' AND 1 = 1;
INSERT OVERWRITE TABLE srm.invoice_lines_temp2 PARTITION(jobid) 
SELECT temp.* FROM srm.invoice_lines_temp2 temp JOIN 
(
SELECT
    source_sys_key,
    legal_company,
    count( DISTINCT concat_ws( '', concat( invoice_line_type ), concat( invoice_head_id ) ) ) 
FROM
    srm.invoice_lines_temp2 
WHERE jobid = '106' 
GROUP BY
    source_sys_key,
    legal_company 
HAVING
    count( DISTINCT concat_ws( '', concat( invoice_line_type ), concat( invoice_head_id ) ) ) = 1 
) t0 ON (temp.source_sys_key = t0.source_sys_key AND temp.legal_company = t0.legal_company )
where temp.jobid = '106';

// 在创建表的时候通过从别的表中查询出相应的记录并插入到所创建的表中
create table invoice_temp1 AS select xx1,xx2,xx3 from invoice;

-----------------------------------------有关于数据导入------------------------------------------


// 删除表中数据，但要保持表的结构定义
dfs -rmr /user/hive/warehouse/srm/invoice_lines;

// 创建外部表
CREATE EXTERNAL TABLE tinvoice_lines(id STRING, name STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/data/test/test_table';
// 导入数据到表中(文件会被移动到仓库目录/data/test/test_table)
load data inpath '/test_tmp_data.txt' INTO TABLE tinvoice_lines;

hive -e "load data local inpath '${SOURCE_PATH}/${SourceFileNameNochar}' overwrite into table srm.invoice_lines_temp1 partition(jobid='${JOBID}');"

// 测试数据库连接
sqoop eval --connect jdbc:mysql://192.168.180.11/angel --username root--password root

// MySQL导入到Hive
sqoop import --connect jdbc:mysql://localhost:3306/test --username root --password 123456 --table person -m 1 --hive-import

// 导出该某Hive表所有数据到MySQL
sqoop export --connect jdbc:mysql://192.168.11.172:16408/ztsrm  --username srm --password handhand  --table invoice_lines  --export-dir /apps/hive/warehouse/srm.db/invoice_lines_temp2/jobid=106 --input-fields-terminated-by ','  --input-null-string "\\\\N" --input-null-non-string "\\\\N"

// 导出该某Hive表指定分区数据到MySQL
sqoop export --connect jdbc:mysql://192.168.11.172:16408/ztsrm  --username srm --password handhand  --table invoice_lines  --export-dir /apps/hive/warehouse/srm.db/invoice_lines_temp2 --input-fields-terminated-by ','  --input-null-string "\\\\N" --input-null-non-string "\\\\N"

INSERT INTO TABLE srm.invoice_lines_temp2 PARTITION (jobid = '106') (
source_sys_key,
status,
material_group 
) 
SELECT
    '${GROUP_NAME}' source_sys_key,
    (
    CASE
        WHEN column28 IN ( '5', 'P', 'V' ) THEN 'VERIFIED' 
        ELSE  column28 
    END   
    ) status,
    IF(column30 IS NULL, '', regexp_replace ( column30, '"', '' ) ) material_group 
FROM
    srm.invoice_lines_temp1 WHERE jobid = '106';

INSERT OVERWRITE TABLE srm.invoice_lines_temp2 PARTITION(jobid) 
SELECT 
    sour_t.* 
FROM 
    srm.invoice_lines_temp2 sour_t where jobid='106';




// 是否启动动态分区，默认false
SET hive.exec.dynamic.partition=true;  
// 打开动态分区后，动态分区的模式，有 strict和 nonstrict 两个值可选，strict 要求至少包含一个静态分区列，nonstrict则无此要求
SET hive.exec.dynamic.partition.mode=nonstrict; 

import  pymysql  
import codecs

  
def getSingleSQL(table,schema = 'srm',ispartition = False):  
    # table =  为表名，mysql, hive表名一致 
    # schema = 为hive中的库名 
    # ispartition : 是否分区默认为分区 

    create_head = 'CREATE TABLE IF NOT EXISTS {0}.{1}('.format(schema,table) + '\n'
    create_tail = 'ROW FORMAT DELIMITED FIELDS TERMINATED BY \',\' ; \n\n'
    connection=pymysql.connect(host='192.168.11.172', port=16408, user='srm', password='handhand', db='srm', charset='utf8')  
    try:  
        with connection.cursor(cursor=pymysql.cursors.DictCursor) as cursor:  
            sql='SHOW FULL FIELDS FROM  {0}'.format(table)  
            cursor.execute(sql) 
            try:  
                for row in cursor: 
                    if 'bigint' in row['Type']:  
                        row['Type'] = "bigint"  
                    elif 'int' in row['Type'] or 'tinyint' in row['Type'] or 'smallint' in row['Type'] or 'mediumint' in row['Type'] or 'integer' in row['Type']:  
                        row['Type'] = "int"  
                    elif 'double' in row['Type'] or 'float' in row['Type'] or 'decimal' in row['Type']:  
                        row['Type'] = "double"  
                    else:  
                        row['Type'] = "string"  
                    create_head += row['Field'] + ' '+ row['Type'] +' comment \'' + row['Comment'] + '\' ,\n'       
            except:  
                print('程序异常!')    
    finally:  
        connection.close()  
    singleSQL = create_head[:-2] + '\n' + ')'+ create_tail
    return singleSQL     



def getTotalSQL():
    connection=pymysql.connect(host='192.168.11.172', port=16408, user='srm', password='handhand', db='srm', charset='utf8')
    try:  
        with connection.cursor(cursor=pymysql.cursors.DictCursor) as cursor:  
            sql='SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=\'SRM\' AND TABLE_TYPE=\'BASE TABLE\' ' 
            cursor.execute(sql)
            try:
                for row in cursor:
                    print(row)
                    tableName = row['TABLE_NAME']
                    singleSQL = getSingleSQL(tableName)
                    f = open('create_hive_table.sql', 'a', encoding='utf-8')
                    f.write(singleSQL) 
            except:  
                print('程序异常了哦!')   
    finally:  
        connection.close()  

getTotalSQL()