druid语法和hive的语法区别 hive的ddl和dml

目录

一、DDL(Data Definition Language 数据库定义语言)

1.创建数据库

2.查询数据库

3.修改数据库

4.删除数据库

5.创建表

管理表（内部表）

外部表

两个表的相互转换

修改表

删除表

清除表

二、DML数据操作

1.数据导入

向表中装载数据（Load）

通过查询语句向表中插入数据（Insert）

查询语句中创建表并加载数据（As Select）

创建表时通过Location指定加载数据路径

Import数据到指定Hive表中

2.数据导出

Hadoop命令导出到本地

Hive Shell 命令导出

Export导出到HDFS上

---

一、DDL(Data Definition Language 数据库定义语言)

1.创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name     --[IF NOT EXISTS] 增强代码的健壮性
[COMMENT database_comment]                        --库的注释
[LOCATION hdfs_path]                              -- 指定对应的hdfs路径
[WITH DBPROPERTIES (property_name=property_value, ...)]; --库的属性

创建库

//数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db
hive (default)> create database db_hive; 
hive (default)> create database if not exists db_hive; //标准写法
hive (default)> create database db_hive2 location '/db_hive2';//指定在hdfs上存放的位置

2.查询数据库

hive> show databases; //查询数据库
hive> show databases like 'db_hive*'; //查询指定要求的数据库
hive> desc database db_hive; //显示数据库信息
hive> desc database extended db_hive; //显示数据库详细信息
hive (default)> use db_hive; //切换数据库

3.修改数据库

   只可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

hive (default)> alter database db_hive set dbproperties('createtime'='20170830');
//设置某些键值对属性

4.删除数据库

hive>drop database db_hive2; //删除空数据库
hive> drop database if exists db_hive2; //标准写法
hive> drop database db_hive cascade; //强制删除数据库

5.创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name   --external 如果不加创建表为内部表
                                                              --加上以后创建表为外部表
[(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 DELIMITED]                    --表的数据对应格式
 [FIELDS TERMINATED BY char]              --字段分隔符  ascii 1号位 ^A  ctrl+v ctrl+a
 [COLLECTION ITEMS TERMINATED BY char]    --集合分隔符  ascii 2号位 ^B  ctrl+v ctrl+b
 [MAP KEYS TERMINATED BY char]            --map的kv分隔符 ascii 3号位 ^C  ctrl+v ctrl+c
 [LINES TERMINATED BY char] ]              --行分隔符   默认值'\n'
 以上的默认值非常不好用
[STORED AS file_format]   --表对应的数据存储的格式  TEXTFILE .txt orc 
[LOCATION hdfs_path]      --表所对应hdfs路径
[TBLPROPERTIES (property_name=property_value, ...)]  --表对应的属性 非常有用的
[AS select_statement]     --根据查询结果来创建一张表 带表的所有结构和数据 唯独不带分隔符
[LIKE table_name]         --模仿一张表 带表的结构不带数据 但是带分隔符

管理表（内部表）

        简介：默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 当删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

create table if not exists student(
id int, name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student'; //普通创建表
create table if not exists student as select id, name from student;//根据查询结果创建表
create table if not exists student3 like student; //根据已经存在的表结构创建表
desc formatted student2; //查看表的详细信息
drop table student2; //删除表

外部表

        简介：因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t'
location '/company/dept'; //创建一个外部表

两个表的相互转换

alter table student set tblproperties('EXTERNAL'='TRUE'); //修改内部表student为外部表
alter table student set tblproperties('EXTERNAL'='FALSE'); //修改外部表student为内部表
注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写

修改表

ALTER TABLE table_name RENAME TO new_table_name //重命名
alter table dept add columns(deptdesc string); //在dept表中增加列
alter table dept change column deptdesc desc string; //更新列
alter table dept replace columns(deptno string, dname
 string, loc string); //替换列

删除表

drop table dept;

清除表

truncate table student; //Truncate只能删除管理表，不能删除外部表中数据

二、DML数据操作

1.数据导入

向表中装载数据（Load）

load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];
（1）load data:表示加载数据
（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
（3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，否则表示追加
（5）into table:表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区

实例

create table student(id int, name string) row format delimited fields terminated by '\t';//创建一张表
load data local inpath '/opt/module/hive/datas/student.txt' into table student;//加载本地文件到hive
dfs -put /opt/module/hive/datas/student.txt /user/student.txt //上传文件到HDFS
load data inpath '/user/student.txt' into table student; //加载HDFS的文件到Hive，上传后文件消失
load data inpath '/user/student.txt' overwrite into table student; //加载数据覆盖表中已有的数据

通过查询语句向表中插入数据（Insert）

insert into table  student values(1,'ll'),(2,'ss'); //基本模式插入数据
insert overwrite table student select id, name from student where id < 5;
//insert into：以追加数据的方式插入到表或分区，原有数据不会删除
//insert overwrite：会覆盖表中已存在的数据
//insert不支持插入部分字段，并且后边跟select语句时，要跟as select区分开

查询语句中创建表并加载数据（As Select）

create table if not exists student3 as select id, name from student; //根据查询的结果创建表

创建表时通过Location指定加载数据路径

create external table if not exists student(
              id int, name string
              )
              row format delimited fields terminated by '\t'
              location '/student';

Import数据到指定Hive表中

import table student  from '/user/hive/warehouse/export/student';
//先用export导出后，再将数据导入。并且因为export导出的数据里面包含了元数据，因此import要导入的表不可以存在，否则报错。

2.数据导出

//将查询的结果导出到本地
insert overwrite local directory '/opt/module/hive/datas/export/student'
select * from student;
//将查询的结果格式化导出到本地
insert overwrite local directory '/opt/module/hive/datas/export/student'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'            
select * from student;
//将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/user/student'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;
注意：由于是overwrite，所以导出路径一定要写具体

Hadoop命令导出到本地

dfs -get /user/hive/warehouse/student/student.txt
/opt/module/hive/datas/export/student.txt;

Hive Shell 命令导出

hive -e 'select * from default.student;' > /opt/module/hive/datas/export/student4.txt;

Export导出到HDFS上

export table default.student to '/user/hive/warehouse/export/student';
//export和import主要用于两个Hadoop平台集群之间Hive表迁移，不能直接导出的本地