hiveql笔记(一)

时间:2022-01-19 18:03:02

1、创建表

create table if not exists mydb.employees{

  name  String COMMENT 'Employee name',

  salary  FLOAT COMMENT 'Empolyee salary',

  subordinates APPAY<STRING> COMMENT 'Names of subordinates',

  deductions MAP<STRING,FLOAT>

        COMMENT 'Keys are deductions names,values are percentages'

  address  STRUCT<street:STRING,city:String,state:STRING,zip:INT>

        COMMENT 'Home address')

  COMMENT 'Desription of the table'

  TBLPROPERITES ('creator '='me','created_at'='2012-01-02 10:00:00',...)

  LOCATION '/usr/hive/warehouse/mydb.db/employees';

Hive 会自动添加两个表属性:一个是last_modified_by,其保存着最后修改这个表的用户的用户名。另一个是last_modified_time,其保存着最后修改的新纪元时间秒。

2、查看表的详细表结构信息

DESCRIBE EXTENDED mydb.employees;

//在表名后添加字段的名称,使用extended关键字也不会增加更多的输出信息。

DESCRIBE mydb.employees.salary;

3、外部表

CREATE EXTERNAL TABLE IF NOT EXISTS stocks (

  exchange  STRING,

  symbol    STRING,

  ymd      STRING,

  price_open  FLOAT,

  price_high   FLOAT,

  price_low  FLOAT,

  price_close  FLOAT,

  volume    INT,

  price_adj_close  FLOAT)

  ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,‘

  LOCATION '/data/stocks';

关键字EXTENAL告诉hive这个表是外部的,而后面的LOCATION..子句则用于告诉HIVE数据位于哪个路径下。

//还可以对一张存在的表进行结构复制(而不会复制数据)

  CREATE EXTERNAL TABLE IF NOT EXISTS mydb.employees3

  LIKE mydb.employees

  LOCATION '/path/to/data';

4、分区表、管理表

CREATE TABLE employees (

  name  String,

  salay  FLOAT,

  subordinates  APPAY<STRING>,

  deductions  MAP<STRING,FLOAT>,

  address  STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>

PARTITIONED BY (country STRING,state STRING);

分区表改变了Hive对数据存储的组织方式,这个表会有一个exployees目录与之对应,每个分区都有一个partXX

//可以使用show partitions命令查看表中存在的所有分区:

SHOW PARTITIONS employees;

//如果表中存在很多的分区,而只想查看是否存储某个特定分区键的分区的话,还可以在命令上添加指定了一个或多个特定分区字段值的PARTITION子句:

SHOW PARTITIONS employees PARTITION(country='US');

SHOW PARTITIONS employees PARTITION(country='US',state='AK');

5、外部分区表

CREATE EXTERNAL TABLE IF NOT EXISTS log_messages (

  hms  INT,

  severity  STRING,

  server    STRING,

  porcess_id  STRING,

  message  STRING)

PARTITIONED BY (year INT,month INT,day INT)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

//添加一个分区

ALTER TABLE log_messages ADD PARTITION(year = 2012,month = 1,day =2 )

LOCATITION 'hdfs://master_server/data/log_message/2012/01/02';

//将一个月前的分区数据拷贝到其他集群

hadoop distcp /data/log_message/2011/12/02  s3n://ourbucket/logs/2011/12/02

//修改表,将分区路径指向到S3路径:

ALTER TABLE log_messages PARTITION(year = 2011,month = 12,day = 2)

SET LOCATION 's3m://ourbucket/logs/2011/01/02';

//DESCRIBE EXTENDED log_message 语句会将分区键作为表的模式的一部分,和partitionKeys列表的内容同时进行显示:

DESCRIBE EXTENDED log_messages;

6、删除表

DROP TABLE IF EXISTS employees;

//这里有个知识点,如果开启了Hadoop回收站功能(默认是关闭),那么数据将会转移到用户在分布式文件系统中的用户跟目录下的.Trash目录下,也就是HDFS中的/usr/$USER/.Trash目录,如果要开启这个功能,需要配置fs.trash.internal的值。设置一个合理的整数,比如如果设置1440,那么就表示是24小时。

7、表重命名

ALTER TABLE log_messages RENAME TO logmsgs;

8、修改列信息

ALTER TABLE log_messages

CHANGE COLUMN hms hours_minutes_seconds INT

COMMENT 'The hours,minutes, and seconds part of the timestamp'

AFTER severity;

//这里说明,如果想将这个字段移动到第一个位置,需要使用FIRST关键字替代AFTER other_column子句即可。

9、增加列

ALTER TABLE log_messages ADD COLUMNS (

  app_name STRING COMMENT 'Application name',

  session_id LONG COMMENT 'The current session id');

10、删除或者替换列

//移除了之前所有的字段并重新指定了新的字段:

ALTER TABLE log_messages REPLACE COLUMNS (

  hours_mins_secs INT COMMENT 'hour,minute,seconds from timestamp',

  serverity STRING COMMENT 'The message severity'

  message STRING COMMENT 'The rest of the message');

11、修改存储属性

ALTER TABLE log_messages

PARTITION(year = 2012,month = 1,day = 1)

SET FILEFORMAT SEQUENCEFILE;

12、通过查询语句向表中插入数据

INSERT OVERWRITE TABLE employees

PARTITION (country = 'US' , state = 'OR' )

SELECT * FROM staged_employees se

WHERE se.cnty = 'US' AND se.st = 'OR';

//如果是非分区表,之前表中的内容将会被覆盖掉

13、动态分区插入

//指定了country字段的值为静态的US,而分区字段state是动态值。

INSERT OVERWRITE TABLE employees

PARTITION (country='US',state)

SELECT ...,se.cnty,se.st

FROM staged_employees se

WHERE se.cnty = 'US';

(静态分区键必须出现在动态分区键之前)

//延伸动态分区属性:

hive.exec.dynamic.partition  false      设置成true,表示开启动态分区功能

hive.exec.dynamic.partition.mode  strict   设置成strict,表示允许所有分区都是动态的

hive.exec.max.dynamic.parition.pernode  100  每个mapper或reducer可以创建的最大动态分区个数。如果某个mapper或reducer尝试创建大于这个值得分区的话则会抛出一个致命错误信息。

hive.exec.max.dynamic.partitions  +1000  一个动态分区创建语句可以创建的最大动态分区个数。如果超过这个值则会抛出一个致命错误信息。

hive.exec.max.created.files  100000  全局可以创建的最大文件个数。

eg:

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrct;

set.hive.exec.max.dynamic.partitions.pernode=1000;

INSERT OVERWRITE TABLE employees

PARTITION (country,state)

SELECT ...,se.cty,se.st

FROM staged_employees se;

14、单个查询语句中创建表并加载数据

CREATE TABLE ca_employees

as SELECT name,salary,address

FROM employees

WHERE se.state = 'CA';

15、导出数据

hadoop fs -cp sorce_path target_path

//也可以使用INSERT...DIRECTORY...

INSERT OVERWRITE LOCAL DIRECTOR 'tmp/ca_employees'

SELECT name,salary,address

From employees

WHERE se.state = 'CA';

参考文献《Hive编程指南》