hive(三) -- 基础语法及导入导出

create database hive01;

create database if not exists hive01;

create database if not exists hive01 location '/hive01.db';

alter database hive01 set dbproperties('createtime'='20220727');

show databases;

show databases like 'h*';

desc database hive01;
describe database extended hive01 ; -- 带属性

use hive01;

drop database hive01;

drop database if exists hive01;

drop database if exists hive01 cascade;

select cast( '100' as double); 会将100字符串转换为100整数值。
如果强制转换失败，例如select cast ('aa' as int );，该函数返回NULL。

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 DELIMITED | SERDE serde_name WITH SERDEPROPERTIES(property_name=property_value,..)] 
[STORED AS file_format] 
[LOCATION hdfs_path]


[] 中括号的语法表示可选。
|  表示使用的时候，左右语法二选一。
建表语句中的语法顺序要和语法树中顺序保持一致。

字段简单说明
-  CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项 来忽略这个异常。
-  EXTERNAL  外部表
-  COMMENT： 为表和列添加注释。
-  PARTITIONED BY 创建分区表
-  CLUSTERED BY 创建分桶表
-  SORTED BY  排序不常用
- ROW FORMAT DELIMITED 使用默认序列化LazySimpleSerDe 进行指定分隔符
- SERDE 使用其他序列化类 读取文件
- STORED AS 指定文件存储类型
- LOCATION 指定表在HDFS上的存储位置。
- LIKE 允许用户复制现有的表结构，但是不复制数据

1,admin,123456,男,18
2,zhangsan,abc123,男,23
3,lisi,654321,女,16

use hive01;

create table t_user(
    id int,
    uname string,
    pwd string,
    sex string,
    age int )
row format delimited fields terminated by ','; --指定 字段之间用 , 分隔

当我们创建表后 在HDFS上会产生对应的文件夹 
/user/hive/warehouse/hive01.db/t_user
但是此时里面没有任何文件 
我们可以将user.txt文件上传到这个位置

select  * from t_user;
+------------+---------------+-------------+-------------+-------------+
| t_user.id  | t_user.uname  | t_user.pwd  | t_user.sex  | t_user.age  |
+------------+---------------+-------------+-------------+-------------+
| 1          | admin         | 123456      | 男           | 18          |
| 2          | zhangsan      | abc123      | 男           | 23          |
| 3          | lisi          | 654321      | 女           | 16          |
+------------+---------------+-------------+-------------+-------------+

liuyan,tangtang_mimi,liuliu:18_yanyan:14,hui long guan_beijing
jinlian,dalang_qingqing,jinjin:18_lianlian:19_aa:20,chao yang_beijing

{
    "name": "liuyan",
    "friends": ["tangtang" , "mimi"] ,       //列表Array, 
    "children": {                      //键值Map,
        "liuliu": 18 ,
        "yanyan": 14
    }
    "address": {                      //结构Struct,
        "street": "hui long guan" ,
        "city": "beijing" 
    }
}

create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
-- 语法解释
row format delimited fields terminated by ','  -- 列分隔符
collection items terminated by '_'  	-- MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':'				-- MAP中的key与value的分隔符

- arr[index]   取值
- size(arr)     长度
- 数组角标越界返回NULL
- explode(arr)

select name,friend[0],friend[1],friend[2] from t_user2;
+----------+-----------+-----------+-------+
|   name   |    _c1    |    _c2    |  _c3  |
+----------+-----------+-----------+-------+
| liuyan   | tangtang  | mimi      | NULL  |
| jinlian  | dalang    | qingqing  | NULL  |
+----------+-----------+-----------+-------+

select name,size(friend) as frendsize from  t_user2;
+----------+------------+
|   name   | frendsize  |
+----------+------------+
| liuyan   | 2          |
| jinlian  | 2          |
+----------+------------+

map_keys(字段)  所有key
map_values(字段) 所有value

select map_keys(children),map_values(children) from t_user2;
+------------------------+----------+
|          _c0           |   _c1    |
+------------------------+----------+
| ["liuliu","yanyan"]    | [18,14]  |
| ["jinjin","lianlian"]  | [18,19]  |
+------------------------+----------+
select map_keys(children)[0],map_values(children)[0] from t_user2;
+---------+------+
|   _c0   | _c1  |
+---------+------+
| liuliu  | 18   |
| jinjin  | 18   |
+---------+------+
-- 根据key获取值
select children['liuliu'] from t_user2
+-------+
|  _c0  |
+-------+
| 18    |
| NULL  |
+-------+

select name,address.city,address.street from t_user2;
+----------+----------+---------------+
|   name   |   city   |    street     |
+----------+----------+---------------+
| liuyan   | beijing  | huilong guan  |
| jinlian  | beijing  | chao yang     |
+----------+----------+---------------+

# 默认位置
/user/hive/warehouse

create table t_user_location(
    id int,
    uname string,
    pwd string,
    sex string,
    age int )
row format delimited fields terminated by ','
location "/aaa";  -- 这里指定在根目录下的aaa文件夹

将数据上传到aaa文件夹中
hdfs dfs -put user.txt /aaa

Location可以指定创建库的位置 也可以指定创建表的位置.

-- 显示表信息
show tables;
show tables like '*user*';

desc t_user;   --字段  类型
desc formatted t_user;  -- 表详细信息

-- 修改表名
-- alter table old_table_name rename to new_table_name
-- alter table test02 rename to test;

--  添加列
-- alter table 表名  add  列名 数据类型;


-- 修改列
-- alter table 表名 change 旧列名 新列名 数据类型



-- 删除列  想删除哪列 就不写哪列 这里的删除就是用 ()中的内容替换原本的内容
select * from t_user_location;
alter  table  t_user_location replace columns (
    id int,
    uname string,
    pwd string,
    sex string);


-- 删除表
-- drop table 表名;

当我们创建好一张表后,会有对应的文件夹 默认位置 /user/hive/warehouse/xx.db/表名
我们之前的方式是通过put或者mv直接将文件放入到对应的文件夹下 

user.txt
1,admin,123456,男,18
2,zhangsan,abc123,男,23
3,lisi,654321,女,16

use hive01;

create table t_user_import(
    id int,
    uname string,
    pwd string,
    sex string,
    age int
)row format delimited fields terminated by ',';

我们可以直接将user.txt 上传到指定的位置 
hdfs dfs -put user.txt /user/hive/warehouse/hive01.db/t_user_import
也可以将hdfs上的数据 移动 或者复制到指定的位置
hdfs dfs -mv  /user.txt /user/hive/warehouse/hive01.db/t_user_import
hdfs dfs -cp  /user.txt /user/hive/warehouse/hive01.db/t_user_import

表的文件夹下可以有多个文件 都可以显示

指定LOCAL，将在本地文件系统中查找文件路径。
若指定相对路径，将相对于用户的当前工作目录进行解释;
用户也可以为本地文件指定完整的URI-例如:file:///root/user.txt
/*
语法
     加载本地文件到 表  注意 这里的本地并不是客户端本地 而是hiveserver2所在的计算机
     load data local inpath '本地文件' into table  表名;  //不覆盖原有数据 如果有相同文件会自动改名
     load data local inpath '本地文件' overwrite into table  表名; //覆盖原有数据
    加载hdfs文件到 表
     load data  inpath '本地文件' into table  表名;  //不覆盖原有数据 如果有相同文件会自动改名
     load data  inpath '本地文件' overwrite into table  表名; //覆盖原有数据
 */
create table t_user_load(
    id int,
    uname string,
    pwd string,
    sex string,
    age int
)row format delimited fields terminated by ',';

-- 注意这是一个复制操作 本地的user.txt还存在   
load data local inpath "/root/user.txt" into table t_user_load;
-- 同名文件可以多次执行 会自动改名
load data local inpath "/root/user.txt" into table t_user_load;
-- 查询没有任何问题
select * from t_user_load;

-- 如果加上 overwite 会将原本文件中的所有文件覆盖
load  data local inpath "/root/user.txt" overwrite into table t_user_load;
-- 查询时只显示最后一次添加的内容
select * from t_user_load;

没有指定LOCAL关键字 如果filepath指向的是一个完整的URI，会直接使用这个URI 
如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.defaultFS指定的(不出意外，都是HDFS)。

-- 将hdfs上的根目录的user.txt 加载  注意这是一个移动的动作 原本位置的文件没有了
load data  inpath "/user.txt" into table t_user_load;
-- 加上overwirte还是覆盖操作
load data  inpath "/user.txt" overwrite into table t_user_load;

insert into t_user_load values(1,'admin','123','男',100);

-- 如果报错  FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask
set hive.txn.stats.enabled=false
set hive.stats.autogather=false

create table test_insert(
    id int,
    name string
)row format delimited fields terminated by ",";

查询user表的id和name 将结果保存到test_insert中;
insert into table test_insert select id,uname from t_user_load;

查询user表的id和name 将结果保存到test_insert中 将原有数据覆盖
insert overwrite table  test_insert select id,uname from t_user_load;

create table test_insert02(
    id int
)row format delimited fields terminated by ",";

create table test_insert03(
    name string
)row format delimited fields terminated by ",";

我想将user表中id一列插入到  test_insert02 表中 将user表中name一列插入到test_insert03表中.
我们可以这样写

insert into table  test_insert02 select id from t_user;
insert into table  test_insert03 select uname from t_user;
但是这样的话 会扫描t_user两次 

一次扫描,多次插入
from t_user
insert into table test_insert02
select id
insert into table test_insert03
select uname;

/*
	 create table 表名 as select 列名,列名 from 表;
*

create  table  t_tmp  as  select id , uname , age from t_user ;

-- 将t_user数据导出到hdfs上 /aaa 文件夹中
export table t_user to '/aaa';
-- 创建表t_user_import 和t_user结构一样
create table t_user_import02 like t_user;
-- 使用import将导出的数据导入到 t_user_import02表中
import  table t_user_import02 from '/aaa'

    格式
        -- 将查询的结果 导出到本地文件夹
        insert overwrite local directory '本地文件夹名' select 查询语句;
        -- 将查询的结果 导出HDFS 不写local
        insert overwrite  directory 'HDFS文件夹名' select 查询语句;

-- 将t_user表中的数据 导出到本地文件夹 这是一个覆盖操作 会覆盖指定文件夹下的所有 如果指定为/ 那么废了
insert overwrite local directory '/data/output' select * from t_user;

--以这种方式导出的文件是使用的是默认分隔符 '\001'
--我们可以自己指定分隔符
insert overwrite local directory '/data/output2'
row format delimited fields terminated by ',' 
select * from t_user;

-- 不写local导出到hdfs上 实际上使用的是 hadoop配置文件中参数fs.default.name设置的值
insert overwrite  directory '/data/output2'
row format delimited fields terminated by ',' 
select * from t_user;

只要将数据获取出来保存即可 
可以选择使用hadoop下载命令 
 hdfs dfs -get /user/hive/warehouse/hive01.db/t_user  /data
 可以使用hive shell命令
 hive  -e  "use hive01 ; select * from t_user ;"  >>  res.data  保存sql结果
 Export导出到HDFS上
 export table t_user to '/aaa';