9つの最も間違いやすいHive sqlの詳しい解と使用の注意事項

本文の小さい提案を読む:本文は細かく噛んでゆっくり飲み込むのに適して、一目十行しないでください、さもなくば多くの価値のある細部を見逃すことができます.
文章は公衆番号に先発する:5分でビッグデータを学ぶ
前言
デジタル倉庫の構築とデータ分析を行う際に最もよく使われるのはsqlであり、その文法は簡潔明瞭で、理解しやすい.現在、ビッグデータ分野のいくつかの主流の枠組みはすべてsql文法を支持している.hive、spark、flinkなどを含む.そのため、sqlはビッグデータ分野でかけがえのない役割を果たしている.
sqlを使用するときに慣れていないか、注意していないと、クエリー分析でエラーが発生しやすくなります.次に、エラーが発生しやすいsql文と使用上の注意点をいくつか見てみましょう.
テキストの開始
1. decimal
hiveはint,double,stringなどの一般的なタイプに加えてdecimalタイプもサポートされ、データベースに正確な数値を格納し、金額を表すフィールドでよく使用されます.
注意事項:
例えば、decimal(11,2)は最大11桁の数字を表し、そのうち後2桁は小数で、整数部分は9桁である.整数部分が9ビットを超えると、このフィールドはnullになり、整数部分が9ビットを超えないと元のフィールドが表示されます.小数部が2位未満の場合、後に0で2位を補い、小数部が2位を超えると、超過部は四捨五入する.直接decimalと書くこともできます.後に桁数を指定せず、デフォルトはdecimal(10,0)整数10ビットで、小数はありません.
2. location

          location            
create  table stu(id int ,name string)  location '/user/stu2';

注意事項:
テーブルを作成するときにlocationを使用します.フォルダを指定すると、hiveはフォルダの下のすべてのファイルをロードします.テーブルにパーティションがない場合、このフォルダの下にフォルダはありません.そうしないと、エラーが発生します.テーブルがパーティションテーブルである場合、partioned by(day string)のように、このフォルダの下の各フォルダはパーティションであり、フォルダ名はday=2020,1123のフォーマットであり、msck repair table scoreを使用する.テーブル構造の修復に成功すると、データがすべてテーブルにロードされていることがわかります.
3.load dataとload data local

 hdfs     
load data inpath '/hivedatas/techer.csv' into table techer;

         
load data local inpath '/user/test/techer.csv' into table techer;

注意事項:

load data localを使用すると、ローカルファイルシステムからロードされ、hdfs上の

にファイルがコピーされることを示します.

load dataを使用してhdfsファイルシステムからロードすると、ファイルはhive関連ディレクトリに直接移動します.コピーは過去ではないことに注意してください.hiveはhdfsファイルが3コピーされていると考えているので、

を再コピーする必要はありません.

表がパーティション表の場合、load時にパーティションを指定しないと

とエラーが発生します.

同じファイル名のファイルをロードすると、

と自動的に名前が変更されます.
4.dropとtruncate

     
drop table score1;

     
truncate table score2;

注意事項:
hdfsが回収局を開いた場合、dropが削除したテーブルデータは回収局から回復することができ、テーブル構造は回復できず、自分で再作成する必要がある.truncateが空になったテーブルはリサイクルステーションに入らないため、truncateが空になったテーブルを復元できません.だからtruncateは必ず慎重に使って、いったん空になったら物理の回復を除いて天に帰ることができません
5.join接続

INNER JOIN    ：                                
select * from techer t [inner] join course c on t.t_id = c.t_id; -- inner    

LEFT OUTER JOIN     ：          ，          
select * from techer t left join course c on t.t_id = c.t_id; -- outer   

RIGHT OUTER JOIN     ：          ，          、
select * from techer t right join course c on t.t_id = c.t_id;

FULL OUTER JOIN   (  )  :                  。                    ，     NULL   。
SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;

注意事項:

hive 2バージョンでは、join on条件の後に記号より大きいものを使用できる不等値接続がサポートされています.さらにjoin on条件後フォローor(以前バージョンon後は=とandのみサポート、><とorはサポートされていません)

もサポートされています

hive実行エンジンがMapReduceを使用すると、1つのjoinが1つのjobを起動し、1つのsql文に複数のjoinがある場合、複数のjob

が起動します.
注:テーブル間のカンマ(,)接続はinner joinと同じです.例:

select tableA.id, tableB.name from tableA , tableB where tableA.id=tableB.id;   
    
select tableA.id, tableB.name from tableA join tableB on tableA.id=tableB.id;   

それらの実行効率には違いはありませんが、書き方が異なるだけで、カンマではsql 89標準、joinではsql 92標準です.後ろのフィルタ条件をカンマで接続するにはwhere、後ろのフィルタ条件をjoinで接続するにはonです.
6. left semi join

            ，        join       ，
         in/exists       ，  in/exists       
SELECT A.* FROM A where id in (select id from B)

SELECT A.* FROM A left semi join B ON A.id=B.id

     sql           ，           

注意事項:

left semi joinの制限は、join句の右側の表はon句にフィルタ条件を設定するしかなく、where句、select句または他の場所でフィルタリングすることはできません.

left semi joinにおけるonの後のフィルタ条件は、等しい番号のみであり、他のものではない.

left semi joinは、テーブルのみを渡すjoin keyがmapフェーズに与えられるため、left semi joinにおける最後のselectの結果は左テーブルのみが現れる可能性がある.

left semi joinはin(keySet)の関係で、右の表の重複記録に遭遇すると、左の表は

をスキップします.
7.集約関数のnull値

hive   count(),max(),min(),sum(),avg()         

注意事項:
集約操作ではnull値に注意してください.
count(*)にはnull値が含まれ、すべてのロー数を統計します.count(id)はidがnullの値を含まない.min最小値を求めるのはnullを含まないので、すべての値がnullでない限りです.avg平均値を求めてもnullは含まれません.
以上、null値が算出エラーの結果を最も招きやすいことに特に注意する必要がある
8.演算子のnull値

hive            (+,-,*,/)  
     (>, 

注意事項:

各行の列フィールドが加算または減算され、null値が含まれている場合、結果はnull

となる.
例:商品表(product)が1枚ある
id
price
dis_amount
1
100
20
2
120
null
各フィールドの意味:id(商品id)、price(価格)、dis_amount(割引金額)
各商品の割引後の実際の価格を計算したいのですが、sqlは以下の通りです.

select id, price - dis_amount as real_amount from product;

結果は次のとおりです.
id
real_amount
1
80
2
null
id=2の商品価格はnullで、結果は間違っています.
null値を処理できます.sqlは次のようになります.

select id, price - coalesce(dis_amount,0) as real_amount from product;

   coalesce      null     ，          

coalesce              
  sql：  dis_amount   ，   dis_amount，    ，   0

より小さいのはnull値を含まない、例えばid<10;idがnull値であることは含まれません.

not inはnull値を含まない.例えばcity not in('北京','上海')は、cityに北京、上海、nullを含まない都市である.

9.andとor
sql文のフィルタリング条件または演算では、複数の条件または複数の演算がある場合、乗算優先度が加減よりも高く、乗算または減算の間に優先度が等しく、前に誰が先に誰を計算するかなどの優先度を考慮します.ではandとorは、andとorの優先度が平等に見え、誰が先に誰を計算しているのか、しかし、andの優先度はorより高い.
注意事項:
例:やはり商品表(product)
id
classify
price
1
でんき
70
2
でんき
130
3
でんき
80
4
家具
150
5
家具
60
6
しょくひん
120
私は電気製品や家具の2種類の中で価格が100より大きい商品を統計したいです.sqlは以下の通りです.

select * from product where classify = '  ' or classify = '  ' and price>100

結果を得る
id
classify
price
1
でんき
70
2
でんき
130
3
でんき
80
4
家具
150
結果は間違っていて、すべての電気製品のタイプを検索しました.なぜならand優先度がorより高いからです.上のsql文は実際に実行されているのは、classify='家具'and price>100を見つけて、classify='電気製品'を見つけます.
正しいsqlはかっこをつけて、まずかっこの中を計算します:

select * from product where (classify = '  ' or classify = '  ') and price>100

最後に
最初の時間は最新のビッグデータ技術を取得して、公衆番号に尽くします:5分でビッグデータを学びます
公衆番号を検索する:5分でビッグデータを学び、より多くのビッグデータ技術を学ぶ!