Spark-Hive

1、表：Hive中的表和关系型数据库中的表在概念上很类似，每个表在HDFS中都有相应的目录用来存储表的数据，这个目录可以通过${HIVE_HOME}/conf/hive-site.xml配置文件中的 hive.metastore.warehouse.dir属性来配置，这个属性默认的值是/user/hive/warehouse(这个目录在 HDFS上)，我们可以根据实际的情况来修改这个配置。如果我有一个表wyp，那么在HDFS中会创建/user/hive/warehouse/wyp 目录(这里假定hive.metastore.warehouse.dir配置为/user/hive/warehouse);wyp表所有的数据都存放在这个目录中。这个例外是外部表。

2、外部表：Hive中的外部表和表很类似，但是其数据不是放在自己表所属的目录中，而是存放到别处，这样的好处是如果你要删除这个外部表，该外部表所指向的数据是不会被删除的，它只会删除外部表对应的元数据;而如果你要删除表，该表对应的所有数据包括元数据都会被删除。

3、分区：在Hive中，表的每一个分区对应表下的相应目录，所有分区的数据都是存储在对应的目录中。比如wyp 表有dt和city两个分区，则对应dt=20131218,city=BJ对应表的目录为/user/hive/warehouse /dt=20131218/city=BJ，所有属于这个分区的数据都存放在这个目录中。

4、桶：对指定的列计算其hash，根据hash值切分数据，目的是为了并行，每一个桶对应一个文件(注意和分区的区别)。比如将wyp表id列分散至16个桶中，首先对id列的值计算hash，对应hash值为0和16的数据存储的HDFS目录为：/user /hive/warehouse/wyp/part-00000;而hash值为2的数据存储的HDFS 目录为：/user/hive/warehouse/wyp/part-00002。

来看下Hive数据抽象结构图

从上图可以看出，表是在数据库下面，而表里面又要分区、桶、倾斜的数据和正常的数据等;分区下面也是可以建立桶的。

二、Hive的元数据

Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性(是否为外部表等)，表的数据所在目录等。 由于Hive的元数据需要不断的更新、修改，而HDFS系统中的文件是多读少改的，这显然不能将Hive的元数据存储在HDFS中。目前Hive将元数据存储在数据库中，如Mysql、Derby中。我们可以通过以下的配置来修改Hive元数据的存储方式

 

执行导出

 

在sqoop中，导出是将hdfs作为一个数据源，而将一个远程的数据库作为目标。将一张表从hdfs导出到数据库时，我们必须在数据库中创建一张用于接收数据的目标表。虽然sqoop可以推断出那个java类型适合存储sql数据类型，但反过来确实行不通。因此，必须由用户来确定哪些类型是最合适的。

例如：我们打算从hive中导出zip_profits表到mysql数据库中。

     ①先在mysql中创建一个具有相同序列顺序及合适sql表型的目标表：

   Create table sales_by_zip(volume decimal(8,2),zip integer);

②接着运行导出命令：

  Sqoop export –connect jdbc:mysql://localhost/hadoopguide –m 1 –table sales_by_zip –export-dir /user/hive/warehouse/zip_profits –input-fields-terminated-by “\0001”

     ③过mysql来确认导出成功：

        mysql hadoopguide –e ‘select * from sales_by_zip’

     注意：在hive中创建zip_profits表时，我们没有指定任何分隔符。因此hive使用了自己的默认分隔符；但是直接从文件中读取这张表时，我们需要将所使用的分隔符告知sqoop。Sqoop默认记录是以换行符作为分隔符。因此，可在sqoop export命令中使用—input-fields-terminated-by参数来指定字段分隔符。

 

字符串：
select length('test') from table
4

select reverse('abc') from table
cba

select concat('ab','cd') from table
abcd

select concat_ws(',','ab','cd') from table
ab,cd

select substr('abcd',2) from table
bcd

select substr('abcd',2,2) from table
bc

select upper('abc') from table
ABC

select ltrim(' abc') from table
abc

select regexp_replace('football','oo|ll','') from table
ftba

select regexp_extract('football','foo(.*?)(all)',2) from table
all


select  get_json_object(
'{"email":"mike@test.net","owner":"mike"}','$.owner'
) from table;
mike

select split('abicdief','i') from table
['ab','cd','ef']

条件判断：
select if(1<2,100,200) from table
100

select case 200 when 50 then 'tom' when 100 then 'mary' else 'jim' end from table
jim

select *  from (
select name ,id ,case 
when id <=5  then 'low salary'
when id >5 and id <=6 then 'middle'
else 'very high'
end 
from table where date='20190215') A

时间函数：
select from_unixtime(152234322,'yyyyMMdd') from table
20181212

select unix_timestamp() from table

select unix_timestamp('20181212 17:55:53','yyyyMMdd HH:mm:ss') from table
1323234063

select to_date('2018-12-12 17:55:53') from table
2018-12-12

select day('2018-12-12 17:53:53') from table
12

select weekofyear('2018-12-12 17:53:53') from table
49

select datediff('2012-12-08','2012-05-09') from table
213

select date_add('2018-12-08',10) from table
2018-12-18

select date_sub('2018-12-08',10) from table
2018-11-28

select * from table where name like 'ja%'

select * from table where id is not null

 
select name, id, cast('2018-12-12' as date) d from table_1
union all
select name, id, actiondate as d from table_2     
#union，每个查询语句返回列的数量和名字需保持一致。使用distinct和默认效果一样，去重。all则不去重


create table explode_table
('block' string,
'goods_id' string,
'sale_info' string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS textfile

a:shandong,b:beijing,c:shanghai|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]

select explode(split(goods_id,',')) as goods_id from explode_table

select goods_id2,sale_info from explode_table LATERAL VIEW explode(split(goods_id,','))goods_subView as goods_id2;

LATERAL VIEW 是将 explode结果转换成一个视图goods_subView,在视图中的单列列名定义为goods_id2，然后在查询的时候引用这个列名就能够查到。