hive导出到rds 时间字段string

1、粘贴就能用。获取基础时间数据(注意我的数据是导出到桌面上，你可以导出到HDFS上也可以请细看代码注释)。

2、上传文件到Linux建表转存到Hive中。

3、建时间维度表。

4、经过Hsql时间函数转换得到各种各样的时间导入数据到Hive的时间维表中。

5、检查数据。

6、Mysql建表，使用sqoop导出数据。

7、检查Mysql数据即可导出时间字段string。

函数说明：

NVL ：给值为 NULL 的数据赋值，

它的格式是

NVL( string1, replace_with)。

它的功能是如果string1 为 NULL，则 NVL 函数返回 replace_with 的值，否则返回 string1 的值，如果两个参数都为 NULL ，则返回NULL。

date_format:格式化时间

date_add:时间跟天数相加

date_sub:时间跟天数相减，类似与add，使用一个就ok

select date_sub('2019-06-29',5);

datediff:两个时间相减

select datediff('2019-06-29','2019-06-24');

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。

分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL， 返回值也将为 NULL。

这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

select concat_ws('-',str1,str2) from XX; //str为string类型的列名

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，

产生 array 类型字段。

练习：把星座和血型一样的人归类到一起

第一步转换：

白羊座,A 孙悟空

射手座,A 大海

白羊座,B 宋宋

白羊座,A 猪八戒

射手座,A 凤姐

合并

EXPLODE(col)：将hive 一列中复杂的 array 或者 map 结构拆分成多行。

LATERAL VIEW //侧写

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和 split, explode 等UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

练习：将**分类中的数组数据展开

select explode(category) from movie_info;

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；

/ 在over(里面使用) /

CURRENT ROW：当前行；

n PRECEDING：往前 n 行数据；

n FOLLOWING：往后 n 行数据；

UNBOUNDED：起点，

UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDEDFOLLOWING 表示到后面的终点；

/ 在over外面使用/

LAG(col,n)：往前第n 行数据；

LEAD(col,n)：往后第 n 行数据；

NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，

对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

数据：

一、查询在 2017 年 4 月份购买过的顾客及总人数

group分组一组一个值，over给每一条数据独立开窗

查询顾客的购买明细及购买总额

要将 cost 按照日期进行累加

先排序

再累加

查询顾客上次的购买时间

下次的购买时间

查询前 20%时间的订单信息

RANK() 排序相同时会重复，总数不会变，可以并列

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

练习：计算每门学科成绩排名

计算每门学科成绩排名

创建虚表：

语法: length(string A)

返回值: int

说明：返回字符串A的长度

语法: reverse(string A)

返回值: string

说明：返回字符串A的反转结果

语法: concat(string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果，支持任意个输入字符串

语法: concat_ws(string SEP, string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符

语法: substr(string A, int start),substring(string A, int start)

返回值: string

说明：返回字符串A从start位置到结尾的字符串

语法: substr(string A, int start, int len),substring(string A, int start, int len)

返回值: string

说明：返回字符串A从start位置开始，长度为len的字符串

语法: upper(string A) ucase(string A)

返回值: string

说明：返回字符串A的大写格式

语法: lower(string A) lcase(string A)

返回值: string

说明：返回字符串A的小写格式

语法: trim(string A)

返回值: string

说明：去除字符串两边的空格

语法: ltrim(string A)

返回值: string

说明：去除字符串左边的空格

语法: rtrim(string A)

返回值: string

说明：去除字符串右边的空格

语法: regexp_replace(string A, string B, string C)

返回值: string

说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。

语法: regexp_extract(string subject, string pattern, int index)

返回值: string

说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。

第三个参数:

0 是显示与之匹配的整个字符串

1 是显示第一个括号里面的

2 是显示第二个括号里面的字段

语法: parse_url(string urlString, string partToExtract [, string keyToExtract])

返回值: string

说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO

语法: get_json_object(string json_string, string path)

返回值: string

说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。

语法: space(int n)

返回值: string

说明：返回长度为n的空字符串

语法: repeat(string str, int n)

返回值: string

说明：返回重复n次后的str字符串

语法: ascii(string str)

返回值: int

说明：返回字符串str第一个字符的ascii码

语法: lpad(string str, int len, string pad)

返回值: string

说明：将str进行用pad进行左补足到len位

语法: rpad(string str, int len, string pad)

返回值: string

说明：将str进行用pad进行右补足到len位

语法: split(string str, string pat)

返回值: array

说明: 按照pat字符串分割str，会返回分割后的字符串数组

语法: find_in_set(string str, string strList)

返回值: int

说明: 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0

返回:int。substr在str中第一次出现的位置，若任何参数为null返回null，若substr不在str中返回0，Str中第一个字符的位置为1

说明：C1 被搜索的字符串

C2 希望搜索的字符串

I 搜索的开始位置,默认为1

J 出现的位置,默认为1

24、使用两个分隔符将文本拆分为键值对：str_to_map(text[, delimiter1, delimiter2])

返回：map

Delimiter1将文本分成K-V对，Delimiter2分割每个K-V对。对于delimiter1默认分隔符是','，对于delimiter2默认分隔符是'='

25、unix_timestamp() 返回当前时间戳。另外，current_timestamp() 也有同样作用。

unix_timestamp(string date) 返回 date 对应的时间戳，date 格式必须为 yyyy-MM-dd HH:mm:ss。

unix_timestamp(string date, string format) 返回 date 对应的时间戳，date 格式由 format 指定。

26、from_unixtime(int/bigint timestamp) 返回 timestamp 时间戳对应的日期，格式为 yyyy-MM-dd HH:mm:ss。

from_unixtime(int/bigint timestamp, string format) 返回 timestamp 时间戳对应的日期，格式由 format 指定。

当join时有一个或多个小表可以装载进内存时可以使用mapjoin完成。

第一种方法是可以加上mapjoin指示

第二种方法是设置 set hiveautoconvertjoin=true;来让hive自动优化。同时可以指定

set hiveautoconvertjoinnoconditionaltask = true; --默认开启

set hiveautoconvertjoinnoconditionaltasksize = 10000000; --默认10M，可以根据需求做调整。

hivemapjoinsmalltablefilesize= 2500000；--早期hive版本小表文件大小设置默认25M。

当数据量比较大启动mapjoin后会造成问题请关闭

set hivemapaggr = true; //是否在 Map 端进行聚合，默认为 True ；

set hivegroupbymapaggrcheckinterval = 100000000; //在 Map 端进行聚合 *** 作的条目数目

1、在关联 *** 作前尽量减小数据集,能先聚合的先聚合、能过滤的先过滤（如设置查询条件、合理设置分区，有分区必须设置分区范围）。

2、关联时数据类型要做到一致，如果不一致请用cast先转换类型。

3、慎用count(distinct) ，容易产生数据倾斜，可以先group by 再count。

4、减少小文件，合理设置输入文件大小、合理设置map job 、reduce job数。

set hivemergemapredfiles=true；--设置合并map文件标识。

set mapredmaxsplitsize=100000000; --设置最大输入文件大小，大于此数值都会进行拆分。

set mapredminsplitsizepernode=100000000;--设置每个节点可处理的最小值。

set mapredminsplitsizeperrack=100000000;--设置每个机架可处理的最小值。

set hiveinputformat=orgapachehadoophiveqlioCombineHiveInputFormat; – 执行前进行小文件合并。

set mapredreducetasks=10; -- 设置reduce的数量

set hiveexecreducersbytesperreducer=1073741824 --设置每个reduce所处理的数据大小

5、选取字段避免用select ，只引用你要用的字段，如select auid,aprice。

6、关联值有null值的情况下，可以将null值过滤出来单独处理或者将null值随机赋值。当存在某key有热点问题，也可以同样处理。

7、合理设置数据模型、文件存储格式有利于查询效率优化。

8、善用union all 合并对于同一个表的查询，有利于整体提高效率。

9、合理使用中间临时表，数据量巨大时，如统计一年的数据，可先小规模聚合如按月聚合生成中间表，最后再合并统计出结果。

10、有order by 要限制输出条数。

11、合理设置并行查询

set hiveexecparallel= true ； --以开启并发执行。

set hiveexecparallelthreadnumber= 10 ; //同一个sql允许最大并行度，默认为8。

    这一周主要学习了 Hive 的一些基础知识，学习了多个 Hive 窗口函数，虽然感觉这些窗口函数没有实际的应用意义，但还是都了解了一下。

：可以实现在窗口中实现逐行累加

其他 avg、count、min、max 的用法一样

#要先有一个统计出每个月总额的表，这里就是 t_access_amount 表，如下图一

# partition by uid：根据uid 分组，order by month ：根据月份排序，

rows between unbounded preceding and current_row：选择 无边界的前面的行和当前行之间的行，最后是求 sum 即和。得到下图二

# 是在窗口求和，而窗口的定义时按照 uid 分区 order by 排序得到的。得到一个字段

preceding：前面的，后来的，往序号变大的方向

following：往后

2 preceding ：表示前2行

3 following ：表示后3行

unbounded preceding：表示从第1行开始，从前面的起点

unbounded following：表示最后一行，从后面的终点

注意：使用 rows between 时，按order by 顺序编号（没有指定order by 会默认排序）需要左边是我号右边是大编号

注意：上面显示的排序结果不太对，所以看起来好像结果是相反的一样，实际上单独拿出来运行是对的，可以看以相同方式排序的 rn 编号。

注意：这上面这些都不支持使用 rows between 语句，row_number() 展示出来的排序会和查询字段的最后一个 over(order by)里面的顺序一样

：分组 TOPN，即可以分组后排序，便于找到最好的几条数据

eg：有如下数据，要查出每种性别中年龄最大的2条数据

1,18，a，male

2,19，b，male

3,22，c，female

4，16，d，female

5，30，e，male

6，26，f，female

分析：如果使用按性别分组，是可以分出两条数据，但是分组的缺点是只能查出聚合函数（聚合函数只能产生一组中的一个值）和分组依据。而这里是要求多条数据（2个）

：用于将分组数据按照顺序切分成 n 片（不一定是平均），然后每一份都编号为1-n，这样就可以拿到想要那一份数据。如果切片不均匀，默认增加第一个切片的分布，例如，14 条记录切3片就切不好，就会切成 6、4、4，其中6那份编号为1。

注意：可以不指定 order by

应用：

    统计各个 cookieid，pv 数最多的前1/3天的数据

：生成数据项再分组中的排名，排名相等会在名次中留下空位

：生成数据项再分组中的排名，排名相等不会再名次中留下空位

注意：上面两者都需要指定 order by，不然排名都是1

根据上图，可知区别：

    rank()：按顺序编号，相同分组排序有相同的排名，但是会占位，后面的排名隔一位，就是成绩一样名次相同，但是后面的名次得低两位。

    dense_rank()：按顺序编号，相同分组排序有相同的排名，后面排名顺序不边就是有并列第几名的情况。

    row_number() ：按顺序编号，不会有相同的编号，即使分组排序是相同的。

：小于等于当前值的行数/分组内总行数，注意这个要指定排序方式，不然全都是1

：分组内当前行的（rank 值-1）/（分组内总行数-1）

总结：排序、切片、编号、的都需要使用 order by，不然会全都是1，但是除了 row_number() 因为这个函数编号不重复且顺延，所以还是会有编号，但是不确定编号逻辑。

：用于获得窗口内往上第n行的值，n>=0

第一个参数为列名

第二个参数为往上第n行（可选，默认为1）

第三个参数为默认值（当往上第n行为NULL时，取默认值，如不指定，则为NULL）

：与lag 相反，用于获取窗口内往下第n 行的值，n>=0

第一个参数为列名

第二个参数为往上第n行（可选，默认为1）

第三个参数为默认值（当往上第n行为NULL时，取默认值，如不指定，则为NULL）

：取分组内排序后，截止到当前行，最后一个值

：取分组内排序后，第一个值

注意：使用窗口分析函数时，要特别注意 order by 的使用，如果使用的不恰当会导致统计的不是我们想要的。 row_number() over() 的展示排序好像时根据最后一个字段的over（order by）来展示的 。

这几个分析函数通常用于 olap 中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如分 时、分、天、月的 UV 数

：表示结果属于哪一个分组集合，注意中间是两个下划线

第一列时按照 month 进行分组的，

第二列时按照 day 进行分组的

第三列时按照 对应month、day 分组统计出来的结果

第四列 grouping__id 表示这一组结果属于哪个分组集合

注意：grouping sets 里面就是说明以什么分组，上面的group by 是指定可以进行组合的分组字段sets 里面的只能使用这里指定的字段，

如，这里 grouping sets(month , day) 表示分别根据 month、day 字段分组，               grouping sets(month,day,(month,day)) 则表示分别根据 month、day、month和day 分组。  

这里group by 和 grouping sets 可以搭配使用，不是这里的专属，并且这里也可以不用 groupind sets

：根据group by 的维度的所有组合进行聚合，就是省略 grouping sets ，直接将group by 后面的字段以各种可能的形式分组，然后union all 得到结果。

等价于下面的语句

：是 cube 的子集，以最左侧的维度为主，从该维度进行层级聚合

上面可以实现一个叫上钻的效果：

    月天的uv==》月的uv==》总的uv

hive的语法和sql语法大部分是是相同的，下面我们来挨个写出来：

1，简单介绍存储原理：

hive和hdfs交互，以及和本地linux交互，分为本地文件和hdfs文件，要说他们的区别，底层都是存在磁盘上，要说区别的话，hdfs分布式文件管理系统，有它独有的文件存储结构也即是能够和分布式文件进行交互的独有存储方式，Linux本地文件存储知识单纯的在Linux *** 作系统存储的文件，所以hive的作用就是和linux本地以及linux上hdfs文件两者进行交互，我可以把本地的文件上传到hive文件中，当然hive只是处理hdfs上文件的一个工具，但是hive也有自己独有的文件存储，表的结构信息，创建的表信息，都在hive文件夹中当然是存储在hdfs上的。

2，语法：

1，把本地的文件加载到hive创建的表中（比如：create table tb_user(id int ,name string ); ）在hive中创建的是有结构的表，然后通过命令：load data local inpath ' 路径 ' into table 创建的表名；

2，加载hdfs上的文件到hive中：load data inpath 'hdfs上的路径' into table 创建的表

3，导出hive表到hdfs上：export table 表名 to 'hdfs路径' ；

4，把hdfs上的表导入到hive表中：import table （hive表 ）from （hdfs上的表）；（import table tb_kk from '/data/a'）当导入的表不存在会自动创建这张表，用于备份，或者数据转移，表的字段名和导入的字段名一样。

5，通过查询hive表中的数据并把查询的数据导出到本地：insert overwrite local directory '本地路径' select from student;

6通过查询hive表中的数据并把查询的数据导出到hdfs上：insert overwrite directory 'hdfs上的路径'

select from student;

7, 把查询的结果插入到 当前的hive表中 : insert overwrite table student partition(month='201708') select id, name from student where month='201709';

8,创建表并加载查询到的数据：create table if not exists tb_text as select from tb_text;(as 关键字 同样)

9,创建表时通过Location指定加载数据路径：create table tb_text (id int , name string ) row format delimited fields terminated by ‘,’ location '/hdfs上的目录'，（记住是目录）。

10，创建分区表，分区表就是能够把不同的数据分开来存放，在查询的时候只需要查询指定的分区就行，这样子能够加快查询效率：create table tb_text (id int ,name string ) partitioned by (month string) （//这里我用month来作为分区条件，分区字段不能是表中已经存在的字段，可以将分区字段看成表的伪列，不参与真实字段的 ） row format delimited fields terminated by ',' ；看一下加载数据到分区表的列子，更好地理解一下，load data local inpath '/opt/module/datas/depttxt' into table defaultdept_partition partition(month='201709');//最后就是给伪列字段赋予分区值，也就是分区的文件名。

11，查看表结构，这个需要了解清楚：desc tb_text 查看基本表信息，desc formatted tb_text ,查看详细信息。

12,当数据库存在数据强制删除时可以用：drop database db_hive cascade;

13,当数据库不存在数据:drop database db_hive;

14,默认创建的表为管理表，也称之为内部表，数据和表结构是在一起的，也就是说，当我删除表的时候，底层数据也会跟着一起删除，设计成这样要求一张表代表一份数据，保证数据的安全性。

15，外部表为只存储表的元数据和结构信息，存储的是表的路径，删除外部表不会删除内部数据，只会把表的元数据信息删除了，不影响数据本身，通常用作共享数据，设计外部表的语法为：create external table tb_text(id int ,name string);

16,创建表的时候复制表结构：create table tb_text like tb_text01;

3,外部表和内部表的转化：

1,修改内部表为外部表:alter table tb_text set tblproperties('EXTERNAL'='TRUE');

2,修改外部表为内部表:alter table tb_text set tblproperties('EXTERNAL'='FALSE'); -- 要求KV的大小写,('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

以上就是关于hive导出到rds 时间字段string全部的内容，包括:hive导出到rds 时间字段string、Hive常用查询函数、Hive中常用的字符串 *** 作等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！