Hive的DML操作

来源:https://www.cnblogs.com/jifengblog/archive/2018/07/07/9278879.html
-Advertisement-
Play Games

1． Load 在將數據載入到表中時，Hive 不會進行任何轉換。載入操作是將數據文件移動到與 Hive表對應的位置的純複製/移動操作。 語法結構: 說明： 1、 filepath 相對路徑，例如：project/data1 絕對路徑，例如：/user/hive/project/data1 完整 U ...


1． Load

　　在將數據載入到表中時，Hive 不會進行任何轉換。載入操作是將數據文件移動到與 Hive表對應的位置的純複製/移動操作。

　　語法結構:

load data [local] inpath 'filepath' [overwrite] into table tablename [partition (partcol1=val1, partcol2=val2 ...)]

 

　　說明：

　　1、 filepath 

　　　　相對路徑，例如：project/data1

　　　　絕對路徑，例如：/user/hive/project/data1

　　　　完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1

　　filepath 可以引用一個文件（在這種情況下，Hive 將文件移動到表中），或者它可以是一個目錄（在這種情況下，Hive 將把該目錄中的所有文件移動到表中）。

　　2、 local

　　如果指定了 local， load 命令將在本地文件系統中查找文件路徑。

　　load 命令會將 filepath 中的文件複製到目標文件系統中。目標文件系統由表的位置屬性決定。被覆制的數據文件移動到表的數據對應的位置。

　　如果沒有指定 LOCAL 關鍵字，如果 filepath 指向的是一個完整的 URI，hive會直接使用這個 URI。 否則：如果沒有指定 schema 或者 authority，Hive 會使用在 hadoop 配置文件中定義的schema 和 authority，fs.default.name 指定了Namenode 的 URI。

　　3、 overwrite

　　如果使用了 overwrite 關鍵字，則目標表（或者分區）中的內容會被刪除，然後再將 filepath 指向的文件/目錄中的內容添加到表/分區中。

　　如果目標表（分區）已經有一個文件，並且文件名和 filepath 中的文件名衝突，那麼現有的文件會被新文件所替代。

2． Insert

　　Hive 中 insert 主要是結合 select 查詢語句使用，將查詢結果插入到表中，例如：

　　　　

insert overwrite table stu_buck select * from student cluster by(Sno);

 

　　需要保證查詢結果列的數目和需要插入數據表格的列數目一致.

　　如果查詢出來的數據類型和插入表格對應的列數據類型不一致，將會進行轉換，但是不能保證轉換一定成功，轉換失敗的數據將會為 NULL。

　　可以將一個表查詢出來的數據插入到原表中, 結果相當於自我複製了一份數據。

　　

　　Multi Inserts 多重插入:　　

from source_table
insert overwrite table tablename1 [partition (partcol1=val1,partclo2=val2)]
select_statement1
insert overwrite table tablename2 [partition (partcol1=val1,partclo2=val2)]
select_statement2..

 

 

　　Dynamic partition inserts 動態分區插入:

insert overwrite table tablename partition (partcol1[=val1], partcol2[=val2] ...)
select_statement FROM from_statement

 

 

　　動態分區是通過位置來對應分區值的。原始表 select 出來的值和輸出 partition的值的關係僅僅是通過位置來確定的，和名字並沒有關係。

 

　　導出表數據

　　語法結構

insert overwrite [local] directory directory1 SELECT ... FROM ...
multiple inserts:
FROM from_statement
insert overwrite [local] directory directory1 select_statement1
[insert overwrite [local] cirectory directory2 select_statement2] ...

 

 

　　數據寫入到文件系統時進行文本序列化，且每列用^A 來區分，\n 為換行符。

3． Select

　　基本的 Select 操作

　　語法結構:

select [all | distinct] select_expr, select_expr, ...
from table_reference
join table_other on expr
[where where_condition]
[group by col_list [having condition]]
[cluster by col_list
| [distribute by col_list] [sort by| order by col_list]
]
[limit number]

 

　　說明 ：

　　　　1、order by 會對輸入做全局排序，因此只有一個 reducer，會導致當輸入規模較大時，需要較長的計算時間。

　　　　2、sort by 不是全局排序，其在數據進入 reducer 前完成排序。因此，如果用 sort by 進行排序，並且設置 mapred.reduce.tasks>1，則 sort by 只保證每個 reducer 的輸出有序，不保證全局有序。

　　　　3、distribute by(欄位)根據指定欄位將數據分到不同的 reducer，分發演算法是 hash 散列。

　　　　4、Cluster by(欄位) 除了具有 Distribute by 的功能外，還會對該欄位進行排序。

　　　　如果 distribute 和 sort 的欄位是同一個時，此時，cluster by = distribute by + sort by

4． Hive join

　　Hive 中除了支持和傳統資料庫中一樣的內關聯、左關聯、右關聯、全關聯，還支持 left semi join 和 cross join，但這兩種 JOIN 類型也可以用前面的代替。

　　Hive 支持等值連接 （a.id = b.id ）,  不支持非等值( (a.id>b.id) ) 的連接，因為非等值連接非常難轉化到 map/reduce 任務。另外，Hive 支持多 2 個以上表之間的 join。

 

　　寫 join 查詢時，需要註意幾個關鍵點：

  • 　　join 時，每次 map/reduce 任務的邏輯

　　reducer 會緩存 join 序列中除了最後一個表的所有表的記錄，再通過最後一個表將結果序列化到文件系統。這一實現有助於在 reduce 端減少記憶體的使用量。實踐中，應該把最大的那個表寫在最後（否則會因為緩存浪費大量記憶體）。

 

  • 　　left ， right 和 full outer 關鍵字用於處理 join 中空記錄的情況　　　　
select a.val, b.val from a left outer join b on (a.key=b.key)

 

　　對應所有 a 表中的記錄都有一條記錄輸出。輸出的結果應該是 a.val, b.val，當a.key=b.key 時，而當 b.key 中找不到等值的 a.key 記錄時也會輸出:

　　　　a.val, null

　　所以 a 表中的所有記錄都被保留了；

　　“a right outer join b”會保留所有 b 表的記錄。

 

  • 　　join 發生在 where 子句 之前

　　如果你想限制 join 的輸出，應該在 where 子句中寫過濾條件——或是在 join 子句中寫。這裡面一個容易混淆的問題是表分區的情況：

select a.val, b.val from a
left outer join b on (a.key=b.key)
where a.ds='2009-07-07' and b.ds='2009-07-07'

 

　　這會 join a 表到 b 表（outer join），列出 a.val 和 b.val 的記錄。where 從句中可以使用其他列作為過濾條件。但是，如前所述，如果 b 表中找不到對應 a 表的記錄，b 表的所有列都會列出null，包括 ds 列。也就是說，join 會過濾 b 表中不能找到匹配 a 表 join key 的所有記錄。這樣的話，left outer 就使得查詢結果與 where 子句無關了。解決的辦法是在 outer join 時使用以下語法：

select a.val, b.val from a left outer join b
on (a.key=b.key and
b.ds='2009-07-07' and
a.ds='2009-07-07')

 

　　這一查詢的結果是預先在 join 階段過濾過的，所以不會存在上述問題。這一邏輯也可以應用於 right 和 full 類型的 join 中。

 

  • 　　join 是不能交換位置的

　　無論是 left 還是 right join，都是左連接的。

select a.val1, a.val2, b.val, c.val
from a
join b on (a.key = b.key)
left outer join c on (a.key = c.key)

 

　　先 join a 表到 b 表，丟棄掉所有 join key 中不匹配的記錄，然後用這一中間結果和 c 表做 join。

 

Insert查詢語句


　　多重插入：

create table source_table (id int, name string) row format delimited fields terminated by ',';
create table test_insert1 (id int) row format delimited fields terminated by ',';
create table test_insert2 (name string) row format delimited fields terminated by ',';

 


from source_table
insert overwrite table test_insert1
select id
insert overwrite table test_insert2
select name;

 

　　動態分區插入

set hive.exec.dynamic.partition=true; #是否開啟動態分區功能，預設false關閉。
set hive.exec.dynamic.partition.mode=nonstrict; #動態分區的模式，預設strict，表示必須指定至少一個分區為靜態分區，nonstrict模式表示允許所有的分區欄位都可以使用動態分區。


　　需求：
　　　　將dynamic_partition_table中的數據按照時間(day)，插入到目標表d_p_t的相應分區中。

　　　　原始表：

create table dynamic_partition_table(day string,ip string)row format delimited fields terminated by ",";

load data local inpath '/root/hivedata/dynamic_partition_table.txt' into table dynamic_partition_table;

 

2015-05-10,ip1
2015-05-10,ip2
2015-06-14,ip3
2015-06-14,ip4
2015-06-15,ip1
2015-06-15,ip2


　　目標表：

create table d_p_t(ip string) partitioned by (month string,day string);

　　動態插入：

insert overwrite table d_p_t partition (month,day)
select ip,substr(day,1,7) as month,day from dynamic_partition_table;

 

　　查詢結果導出到文件系統

　　　　將查詢結果保存到指定的文件目錄（可以是本地，也可以是hdfs）

insert overwrite local directory '/root/123456'
select * from t_p;

 

insert overwrite directory '/aaa/test'
select * from t_p;

 

關於hive中的各種join

　　準備數據

1,a
2,b
3,c
4,d
7,y
8,u

2,bb
3,cc
7,yy
9,pp

 

　　建表：

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

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

　　導入數據：

load data local inpath '/root/hivedata/a.txt' into table a;
load data local inpath '/root/hivedata/b.txt' into table b;


　　實驗：
　　　　** inner join

select * from a inner join b on a.id=b.id;

select a.id,a.name from a join b on a.id = b.id;

select a.* from a join b on a.id = b.id;

+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
+-------+---------+-------+---------+--+

 

 

　　　　**left join

select * from a left join b on a.id=b.id;


+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 1 | a | NULL | NULL |
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 8 | u | NULL | NULL |
+-------+---------+-------+---------+--+

 

 

　　　　**right join


select * from a right join b on a.id=b.id;

select * from b right join a on b.id=a.id;


+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
| NULL | NULL | 9 | pp |
+-------+---------+-------+---------+--+

 


　　　　**full outer join

select * from a full outer join b on a.id=b.id;


+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 1 | a | NULL | NULL |
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 8 | u | NULL | NULL |
| NULL | NULL | 9 | pp |
+-------+---------+-------+---------+--+


　　　　**hive中的特別join

select * from a left semi join b on a.id = b.id;


+-------+---------+--+
| a.id | a.name |
+-------+---------+--+
| 2 | b |
| 3 | c |
| 7 | y |
+-------+---------+--+


　　　　相當於

select a.id,a.name from a where a.id in (select b.id from b); 在hive中效率極低

select a.id,a.name from a join b on (a.id = b.id);

select * from a inner join b on a.id=b.id;


　　　　cross join（##慎用）

　　返回兩個表的笛卡爾積結果，不需要指定關聯鍵。

select a.*,b.* from a cross join b;

 

內置jason函數

select get_json_object(line,'$.movie') as moive,get_json_object(line,'$.rate') as rate from rat_json limit 10;

 

 

transform案例:

　　　　1、先載入rating.json文件到hive的一個原始表 rat_json

create table rat_json(line string) row format delimited;
load data local inpath '/root/hivedata/rating.json' into table rat_json;

 

　　　　2、需要解析json數據成四個欄位，插入一張新的表 t_rating

drop table if exists t_rating;
create table t_rating(movieid string,rate int,timestring string,uid string)
row format delimited fields terminated by '\t';

 

insert overwrite table t_rating
select get_json_object(line,'$.movie') as moive,get_json_object(line,'$.rate') as rate,get_json_object(line,'$.timeStamp') as timestring, get_json_object(line,'$.uid') as uid from rat_json limit 10;


　　　　3、使用transform+python的方式去轉換unixtime為weekday
　　　　　　先編輯一個python腳本文件
　　　　　　########python######代碼

vi weekday_mapper.py
#!/bin/python
import sys
import datetime

for line in sys.stdin:
line = line.strip()
movieid, rating, unixtime,userid = line.split('\t')
weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '\t'.join([movieid, rating, str(weekday),userid])

 

　　　　保存文件
　　　　然後，將文件加入hive的classpath：
　　　　　　hive>add FILE /root/hivedata/weekday_mapper.py;

create table u_data_new as select
transform (movieid, rate, timestring,uid)
using 'python weekday_mapper.py'
as (movieid, rate, weekday,uid)
from t_rating;

select distinct(weekday) from u_data_new limit 10;

 

desc formatted student;

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • linux-2.6.22.6內核啟動流程分析之配置
    配置過程最終結果是生成.config文件，我們想要對配置的目的有很清楚的瞭解，必須先對.config文件進行分析。通過cd命令切換到linux-2.6.22.6內核目錄，輸入vi .config 可以看到如下圖所示內容（截圖僅部分內容）： 分析.config文件被什麼文件使用，為了簡便化我們以網卡D ...
  • 【二代示波器教程】第11章 示波器設計—功能模塊劃分
    第11章 示波器設計—功能模塊劃分 二代示波器的界面上做了五個按鈕，分別用於不同功能的配置，本章節就為大家講解這五個按鈕實現的功能。 11.1 主界面上的五個按鈕 11.2 Measure測量功能 11.3 ADC電壓測量功能 11.4 DAC信號發生器 11.5 Math數字信號處理 11.6 S ...
  • 【分享】【原創開源應用第4期】給ili9488，RA8875類顯示屏的emWin底層增加DMA加速方案
    說明：1、emWin底層中最重要的一個優化就是16bpp繪製，特此為其增加DMA加速，已經支持RA8875和ili9488。2、使用中務必將emWin任務設置為除了空閑任務，統計任務以外的最低優先順序，這樣就可以有效的發揮DMA的優勢，CPU可以解放出來處理其他任務。操作說明：1. 所有界面支持按鍵操 ...
  • Ubuntu 創建快捷方式
    方法一：在/usr/share/applications/目錄下創建 .desktop文件，其中Icon 和 Exec是指向啟動圖標和啟動文件。 方法二：使用 gnome-desktop-item-edit 創建快捷方式。 1. 未安裝的先安裝gnome-desktop-item-edit， Ubu ...
  • dockerfile創建鏡像
    Dockerfile 語法 1.ADD ADD命令有兩個參數，源和目標。它的基本作用是從源系統的文件系統上複製文件到目標容器的文件系統。如果源是一個URL，那該URL的內容將被下載並複製到容器中 2.ENTRYPOINT 配置容器啟動後執行的命令，並且不可被 docker run 提供的參數覆蓋，每 ...
  • Mysql基礎1-基礎語法-欄位類型
    主要： 基礎 基本概念 1） 資料庫分類 層次資料庫，網狀資料庫，關係資料庫 常見：SQL Server, Oracle,infomix,sybase,ibmDB2,Mysql 2）資料庫存儲的本質和表現: 存儲本質： 將 數據值 通過數據管理系統進行有序的存儲 存儲表現： 每個資料庫對應一個文件夾 ...
  • Hive命令行及參數配置
    1 ． Hive 命令行 輸入$HIVE_HOME/bin/hive –H 或者 –help 可以顯示幫助選項： 說明： 1、 -i 初始化 HQL 文件。 2、 -e 從命令行執行指定的 HQL 3、 -f 執行 HQL 腳本 4、 -v 輸出執行的 HQL 語句到控制台 5、 -p <port> ...
  • MySQL集群結構說明
    在以前，資料庫的集群配置一直很難，難點在於MySQL主從結構的高可用和讀寫分離。萬幸的是，Galera/GR的出現，讓整個集群的配置都極大程度地簡化了。 以下是一個簡單的MySQL集群拓撲圖： 1.MySQL中間件：對MySQL Server的讀寫操作進行路由(即讀寫分離)；分庫分表(shardin ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類