hive sql学習ノート

5415 ワード
ビッグデータ データベース
1、coalesce
    構文:
COALESCE ( expression [ ,...n ] )
パラメータ:
expressionの任意のタイプの表現。
戻りのタイプ:
データタイプの優先度が一番高いものを返します。 expression のデータタイプを指定しますすべての表現がNullではない場合は、結果のタイプもNullではありません。
コメント
 
  全てのパラメータがNULLの場合、 COALESCE 戻る NULLです。 少なくとも一つは必要です Null 値は NULL を選択します
 
比較 COALESCE 和 CASE
COALESCE 式は CASE 数式の構文ショートカット。すなわち、最適化器に問い合わせてコードを作ります。  COALESCE(expression 1,…n) 次のように書き換えます CASE 式:CASEWHEN (expression1 IS NOT NULL) THEN expression1WHEN (expression2 IS NOT NULL) THEN expression2...ELSE expressionNENDこれは、入力値（expressitions 1、expression 2、expressitionN）を複数回計算することを意味します。 などがありますまた、該当するためには SQL 標準的に、サブクエリーを含む値式は不確定と見なされ、サブクエリーは2回計算されます。それぞれの場合、最初の計算と後続の計算は異なる結果を返すことができる。
 
2、ハヴィng子文
sqlの中でhaving子文はwhere子文と似ています。条件の設定を表しています。それらの違いは、having子文は検索の過程で、重合文（sum、min、max、avg、count）より遅いです。一方、where子文は検索過程では、集合文（sum、min、max、avg、count）より速くなります。
簡単に言えば、  where子文：  select sum(num)as rmb from order where id>10  //先にIDが10より大きいレコードを調べてこそ、語句をまとめることができます。  ハヴィンの句:  select report sto as manager、count(*)as reportfrom eployes  group by report sto having count（*）>4  
//havingの条件式count()は集合文ですので、havingの実行は集合文countより遅くなります。whereに換えるとエラーが発生します。
//パケットデータを集計する場合は、集計文を使用して、パケットデータを再判定する場合は、Havingを使用します。これらの関係を使わないとハミングは存在しません。直接whereを使えばいいです。 
Havingとは、Whereのパケットデータの判断における不足を補うためのものです。whereは語句をまとめるのが早いからです。
 
いつWHEREを使うべきですか？いつHAVINGを使うべきですか？まだ迷っています。以下の説明に従ってください。   WHERE文はGROUTPにあります。 BY文の前SQLは、パケットの前にWHERE文を計算する。   HAVING文はGROUTPにあります。 BY文の後SQLはグループ化後にHAVING文を計算します。
3、mapjoin
MAPJOINを使う時、注意が必要です。1、LEFT OUTER JINの左の表は大きい表でなければなりません。2、RIGHT OUTER JINの右の時計は大時計でなければなりません。3、INNER JOIN左表または右表はすべて大きい表として使用できます。4、FULL OUTER JOINはMAPJOINを使用できません。5、MAPJOINは小表をサブクエリとしてサポートしています。6、MAPJOINを使用する場合は、小表またはサブクエリを参照する必要があります。別名を参照する必要があります。7、MAPJOINでは、不等値で接続またはORで複数の条件を接続することができます。
mapjoinには小さい時計が書いてあります。そして、left outerjinの時、小さい時計はjoinの後ろに書いてあります。
hiveではmapjoinを使用すると、sql文の実行効率が大幅に向上する場合があります。
その原理は、小さいテーブルを全部メモリに取り込んで、mapの時に別のテーブルのデータとメモリの中のテーブルのデータを直接持ってマッチングして、join操作をして、reduceを省きました。
（1）「関連操作にはテーブルが非常に小さく、もう一つのテーブルが大きい」というシーンがありますが、mapjoinはデータの傾きによって、あるレコードにデータが多すぎて失敗しません。
例:SELECT /*+ MAPJOIN(b) */  a.key, a.valueFROM a JOIN b ON a.key = b.key
  • does not need a reducer.For evermapper of A，B is read complettery.The restication is that a FULL/RIGHT OUTER JIN b cannot be performed.
    • （2）「不等値接続操作」が必要なシーン（a.xなど）
    例:
    　　 
    select /*+ MAPJOIN(a) */
  a.start_level, b.*
 from dim_level a
  join (select * from test) b
 where b.xx>=a.start_level and b.xx
    サイズ表の関連と不等値の関連が必要な場合は、mapjoinを使うとより顕著な効果があります。特に大きい表のデータの傾きが比較的大きい場合。
    例:
    mapjoinを使う前:
    create table hive_no_mapjoin as 

　　select f.id,f.dt, coalesce(k.amt,0.0) amt from(

　　select a.id,t.dt from hive_dt t join (select id, min(dt) min_dt from hive_mapjoin group by id) a
　　where t.dt>= a.min_dt) f 
　　left outer join hive_mapjoin k on f.dt = k.dt and f.id = k.id;
    mapjoinを使用した後:
    create table hive_ok_mapjoin as select f.id,f.dt, coalesce(k.amt,0.0) amt from(

select /*+ mapjoin(t) */ a.id,t.dtfrom hive_dt t 
join (

select id,  min(dt) min_dt from hive_mapjoin group by id) a 
where  t.dt>= a.min_dt) f 
left outer join tmp.tst1 kon f.dt = k.dt and f.id = k.id;
    相互リンク:http://www.cnblogs.com/tmeily/p/4250858.html
    　　　　　　　http://www.ithao123.cn/content-579190.html
     
    4、hiveの中で、group byは多くのreduceが必要です。order by rand（）は多くのmap reduceが必要です。だから、データ量が大きい場合、この二つの動作が同時に実行されると、資源が消耗します。解決の方法は2ステップに分けて、order by rand（）limit nを取り出してから、この結果の中からgroup byp;
     
    5、hiveでは、selectの複数のフィールドが必要ですが、distinctのあるフィールドがその値を並べて並べば、使用できます。
    select cid，count from credit.vecmuplairs where dt={CUR＿DATE}and cid！='0'group by cid;
    count(distinct cid)の列を無視すればいいです。
    ソースを参照:http://www.jb51.net/article/24717.htm
     
    6、あるテーブルのあるパーティションのhdfsパスステートメントを確認します。
    use database;
    show table exteded like table partition(dt='%Y%d')
     
    7、ランダムサンプリングの語句：（1）select*from tableame TABLESAMPLE（n PERCENT|ROWS|ByteLengthLiteral）where…
    このうち、n percentはn%の比例データを抽出します。
    （しかし、この方法で結果が出たデータは本当に比例で抽出されていません）
    (2)
    select*from mymybale distribute by rand()sort by rand()limit 10000;(親測定は信頼できる)
     
    参考:http://www.joefkelley.com/736/
    　　　http://www.aichengxu.com/other/9663663.htm
    　　   http://lxw1234.com/archives/2015/08/444.htm
    8、hiveのフィールドタイプの変換
    cast(field as type)
    または:
    convert(field，type)
     
     9、explin
    explinを使ってhql文の運行の詳しい情報を調べられます。
    たとえば:
    explain select a.key c1, a.value c2, b.key c3, b.value c4 from src a join src_skewjoin1 b on a.key = b.key;

    転載先:https://www.cnblogs.com/zichun-zeng/p/5899946.html
    素数のふるい方
    SQL常用データタイプまとめ