hive学习3（hive基本操作）

hive基本操作

hive的数据类型

1）基本数据类型

• TINYINT,SMALLINT,INT,BIGINT
• FLOAT/DOUBLE
• BOOLEAN
• STRING

2）复合类型

• ARRAY：一组有序字段。字段的类型必须相同，例Array(1,2)
• MAP：一组无序的键/值对。键的类型必须是原子的，值可以是任何类型，同一个映射的键的类型必须相同，值得类型也必须相同。例Map('a',1,'b',2)
• STRUCT：一组命名的字段。字段类型可以不同。例Struct('a',1,1,0)

hive 默认分隔符

 
 :分隔行【lines terminated by '	'】
 ^A :分隔字段，显示编码用01【fields terminated by '01'】
 ^B :分隔复合类型的元素，显示编码用02【collectionitems terminated by '02'】
 ^C :分隔map元素的key和value，显示编码用03【map keys terminated by '03'】

DDL操作

Create/Drop/Alter/Use Database

创建数据库【Create Database】

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

实例

hive> create database if not exists wujiadong
    > comment 'the first database'
    > location '/hive_test'
    > with dbproperties('creator'='wujiadong','data'='2016-11-13')

查看所有数据库【show databases】

hive> show databases;
hive> show databases like "w.*";  #正则匹配

删除数据库【Drop Database】

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

实例

hive> drop database wujiadong1;
hive> drop database if exists wujiadong1 cascade; #删除代表的数据库

修改数据库【Alter Database】

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; 

实例

hive> alter database  wujiadong set dbproperties ('edit_by'='wjd'); 修改数据库属性，但不能删除或重置

使用数据库【Use Database】

USE database_name;
USE DEFAULT;

实例

hive> use wujiadong;
hive> use default;

显示某个数据库信息【desc database】

hive> desc database wujiadong;

Create/Drop/Truncate Table

创建表【create table】

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
   [(col_name data_type [COMMENT col_comment], ...)] 
   [COMMENT table_comment] 
   [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
   [CLUSTERED BY (col_name, col_name, ...) 
   [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
   [ROW FORMAT row_format] 
   [STORED AS file_format] 
   [LOCATION hdfs_path]

注释

• CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常
• EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据
• LIKE 允许用户复制现有的表结构，但是不复制数据

ROW FORMAT 
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] 
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] 
   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

• 用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive通过 SerDe 确定表的具体的列的数据

STORED AS 
SEQUENCEFILE|TEXTFILE|RCFILE

• 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

实例

hive> create table student(id int,name string) row format delimited fields terminated by '	'; 创建表

hive> create  table if not exists student1 like student; 创建一个和表一样模式的表

hive> create table if not exists mytable(sid int,sname string)
    >  row format delimited fields terminated by '05' 
    >  stored as textfile; 创建内部表
    
hive> create external table if not exists pageview(
    >  pageid int,
    >  page_url string comment 'the page url'
    > )
    > row format delimited fields terminated by ','
    > location 'hdfs://192.168.220.144:9000/user/hive/warehouse'; 创建外部表
    
hive> create table student_p(id int,name string,sexex string,age int,dept string)
    >  partitioned by(part string)
    >  row format delimited fields terminated by ','
    >  stored as textfile;    创建分区表

查看表信息【describe】

hive> desc student;

hive> desc pageview.pageid; 查看表某列的信息

删除表【drop table】

hive> drop table if exists student;

Alter Table/Partition/Column

ALTER TABLE table_name RENAME TO new_table_name; 重命名

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
partition_spec:
: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...) 增加分区

ALTER TABLE table_name DROP partition_spec, partition_spec,...  删除分区

ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location"; 修改表或分区路径



ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)  增加列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] 更新列

实例

hive> alter table student1 rename to student; 重命名

hive> alter table student_p add if not exists
    >  partition(part='a')  
    >  partition(part='b'); 增加分区
    
hive> alter table student add if not exists
    > partition(year=2011,month=1,day=1) location 'logs/2011/01/01'
    > partition(year=2011,month=1,day=2) location 'logs/2011/01/02';    增加分区

hive> alter table student_p drop partition(part='b'); 删除分区
hive> ALTER TABLE log_messages DROP IF EXISTS PARTITION (year=2011,month=1,day=4); 删除分区

hive> show partitions student_p; #显示表分区


hive> alter table student add columns (gender string comment 'student  gender'); 增加列
hive> alter table student replace columns(id int, age int, name string); 替换所有字段

修改表属性

hive> ALTER TABLE log_messages SET TBLPROPERTIES(
    > 'wujiadong');

修改存储属性

hive> ALTER TABLE log_messages
    > PARTITION(year=2011,month=1,day=1)
    > SET FILEFORMAT SEQUENCEFILE;

外部表【external】

hive> CREATE EXTERNAL TABLE IF NOT EXISTS  stocks5(
    >   home 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';

自定义存储格式

hive> CREATE TABLE employees(
    > name STRING,
    > salary FLOAT,
    > subordinates ARRAY<string>,
    > deductions MAP<STRING,FLOAT>,
    > address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>)
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY '01'
    > COLLECTION ITEMS TERMINATED BY '02'
    > MAP KEYS TERMINATED BY '03'
    > LINES TERMINATED BY '
'
    > STORED AS TEXTFILE;

DML操作

Loading files into tables

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

注释

• Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置

• filepath
  相对路径，例如：project/data1
  绝对路径，例如：/user/hive/project/data1
  包含模式的完整 URI，列如：hdfs://namenode:9000/user/hive/project/data1

• LOCAL关键字
  如果指定了 LOCAL， load 命令会去查找本地文件系统中的 filepath。
  如果没有指定 LOCAL 关键字，则根据inpath中的uri 查找文件

• OVERWRITE关键字
  如果使用了 OVERWRITE关键字，则目标表（或者分区）中的内容会被删除，然后再将filepath指向的文件/目录中的内容添加到表/分区中
  如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

创建内部表students
hive> create table students(id int,name string,gender string)
    > row format delimited fields terminated by ','
    > stored as textfile;

创建外部表students1
hive> create external table students1(id int,name string,gender string)
    > row format delimited fields terminated by ','
    > stored as textfile;

本地导入数据
hive> load data local inpath "/root/hive_test/students.txt" into table students;
加入overwrite 会先删除之前存在的数据在插入数据
hive> load data local inpath "/root/hive_test/students.txt" overwrite into table students;

HDFS导入数据
[root@spark1 hive_test]# hadoop fs -put /root/hive_test/students.txt  /hive_test/   #先将将文件上传到hdfs    
hive> load data inpath '/hive_test/students.txt' into table students; 数据导入hive表中
hive> load data inpath '/hive_test/students.txt' into table students1; 数据导入外部表中

查询是否导入成功
hive> select * from students;