HiveQL:数据

数据定义

1. 创建数据库 CREATE DATABASE （if not exists） name location ;
2. show database like ‘g.*’;
3. drop database if exists name cascade;
4. create table if not exists database.tablename()(like db.tableName);
5. alter table tableName rename to newName;
6. alter table tableName
   change column colName colNewName INT
7. alter table tableName add columns(
   app_name String comment ‘application name’,
   session_id long comment ‘The current session id’);
8. 删除或者替换列

数据操作

1. 装载数据
   LOAD DATA LOCAL INPATH ‘${env:HOME}/california-employees’
   OVERWRITE INTO TABLE tableName
   PARTITION (country = ‘US’,state = ‘CA’)
2. INSERT OVERWRITE TABLE tablename partition (country = ‘US’ , status = ‘OR’) select * from tableName se where se.cnty = ‘US’ and se.st = ‘OR’
3. 动态分区：
   INSERT OVERWRITE TABLE tablename PARTITION (country,state) SELECT …,se.cnty,se.st FROM staged_emplayees se;
4. 导入部分数据
   CREATE TABLE new AS SELECT name,salary,address FROM oldTables WHERE …
5. 导出数据
   

查询

1. 表生成函数：
   
2. 抽样查询
   select * FROM numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON rand()) s;