【hadoop】16、学习hive操作语句，hadoop16hive语句

学习DDL语句

创建对象的语句

Create/Drop/Alter Database

Create Database

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

Drop Database

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|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;

Use Database
USE database_name;
USE DEFAULT;

Hive运行的时候，元数据存储在关系系数据库里面。

Hive运行的时候需要有映射关系的数据，需要快速地读取

Linux里面其实有自带的关系数据库，但是十分不稳定，所以我们不用这个数据库

我们自己搭建一个关系数据库

安装一个关系数据库（mysql）

我们在安装Linux的时候已经安装了mysql

启动mysql

查看mysql是否已经进行监听

3306端口，对的

连接mysql

受限我们需要驱动

设置mysql中远程登录的问题

输入use mysql
select * from user;

grant all on . to root@’%’ identified by ‘123456’;
这个是给所有的用户在所有的数据库上的所有的表的所有权限，密码是123456

查看一下是否成功

修改配置文件

配置mysql路径

修改用户名和密码

我们创建一个hive的数据库

进入hive

启动之后推出hive

Quite；

然后在mysql中查看表

退出

学习hive的DDL语句

Create Table

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(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]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)

primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)

array_type
  : ARRAY < data_type >

map_type
  : MAP < primitive_type, data_type >

struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>

union_type
   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)

row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

例子：

id     int,
date   date,
name   varchar

create table table_name 
(
  id                int,
  dtDontQuery       string,
  name              string
)
partitioned by (date string)

一个例子

CREATE TABLE page_view
(
viewTime INT, 
userid BIGINT,
page_url STRING, 
referrer_url STRING,
ip STRING COMMENT 'IP Address of the User'
)
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'  这个是分隔符，行的每一列用什么分割
STORED AS SEQUENCEFILE;

我们创建一张表

在hive中

create table t_emp
(
id int,
name string,
age int,
dept_name string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

我们在Linux中建立一个文本的数据文件
Emp.txt

导入数据

Loading files into tables
Hive does not do any transformation while loading data into tables. Load operations are currently pure copy/move operations that move datafiles into locations corresponding to Hive tables.

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

Hive通过我们的环境变量找到hadoop在哪，然后连上hadoop，就会创建hive的工作目录在hdfs上，在user下的hive下

我们查询，在hive下面

select count(*) from t_emp;

Hive还可以使用各种集合类型

create table t_person
(
id int,
name string,
like array<string>,
tedian map<string, string>
)
row format delimited
fields terminated by ','
collection items terminated by '_'
map keys terminated by ':';

数据格式
1,,zhangsan,sports_books_TV,sex:男_color:red

加载文件
Load data local inpath ‘root/data.exe’ into table t_person

Hive在运行的时候有一些元数据需要保存。默认保持到DBMS。

学习DML语句

导入数据
Loading files into tables

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

创建分区表

分区实际是一个文件夹，表名就是文件夹名。每个分区，实际上是表名这个文件夹下面的不同文件。分区可以根据时间，地点等等进行分区，比如，每天一个分区，等于每天存每天的数据，或者每个城市，存放每个城市的数据。每次查询数据的时候，只要写下类似where pt=2010_08_23这样的条件即可查询指定时间的数据

Create table sxtstu(id int, sname string, city string)
Partitioned by (ds string) row format delimited fields terminated by ‘,’ stored as textfile;

我们保存数据的时候
Load data local inpath ‘sxtstu.txt’ overwrite into table sxtstu partition(ds=’2013-07-09’);

Copying data from file:/home/Hadoop/sxtstu.txt
Copying file:file:/home/Hadoop/sxtstu.txt

Loading data to table default.sxtstu partition (ds=2013-07-09)
OK
我们尝试创建一张表

create table dept_count(
     dname string,
     num int)
     ;


insert into table dept_count select dept_name,  count(1) from t_emp group by dept_name;

关于分区：

Create table dept_count
(
Num int
)
Partitioned by (dname string);

Insert into table dept_count 
partition (dname='销售部') 
select count(1) 
from t_emp 
where dept_name='销售部' 
group by dept_name

一些案例：

CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2))
  CLUSTERED BY (age) INTO 2 BUCKETS STORED AS ORC;

INSERT INTO TABLE students
  VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);


CREATE TABLE pageviews (userid VARCHAR(64), link STRING, came_from STRING)
  PARTITIONED BY (datestamp STRING) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC;

INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23')
  VALUES ('jsmith', 'mail.com', 'sports.com'), ('jdoe', 'mail.com', null);

INSERT INTO TABLE pageviews PARTITION (datestamp)
  VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');

关于import和export

EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])]
  TO 'export_target_path'

IMPORT [[EXTERNAL] TABLE new_or_original_tablename [PARTITION (part_column="value"[, ...])]]
  FROM 'source_path'
  [LOCATION 'import_target_path']

导出语句

学习数据查询语句

类似SQL语句

create table t_stu
(
userid int,
name string,
age int,
sex int,
classid int
)
row format delimited fields terminated by ','
stored as textfile;

create table t_class
(
cid int,
name string,
teacher string
)
row format delimited fields terminated by ','
stored as textfile;

load data inpath '/pub/student.txt' into table t_stu;

1,zs,32,2,2
2,lis,23,1,2
3,ww,21,1,1

select s.*, c.name from t_stu s join t_class c on s.classid=c.cid;

版权声明：本文为博主原创文章，未经博主允许不得转载。