hive 使用技巧筆記

例子：

INSERT OVERWRITE TABLE prices_collected_${hiveconf:wid_version}

select 

pc.collect_id as product_id ,

regexp_extract(pc.price,'(\\d*\\.?\\d+)',1) as price ,

pc.region,

'' as location_area_code,

'' as city_code,

from_unixtime(unix_timestamp() , 'yyyy-MM-dd hh:mm:ss') as created_at,

from_unixtime(unix_timestamp() , 'yyyy-MM-dd hh:mm:ss') as updated_at

from products_compared_${hiveconf:wid_version} as pc

1.根據hive執行的參數來動态的設定表名稱 prices_collected_${hiveconf:wid_version}

hive -hiveconf wid_version='4' 

則可以通過${hiveconft:wid_version}來接收參數，生成prices_collected_4這張表

2. 使用正規表達式擷取需要的資訊，如：擷取一段字元串中的數字

regexp_extract(pc.price,'(\\d*\\.?\\d+)',1) as price

注意hive中需要使用雙斜杠來處理正規表達式

3. 擷取系統時間

from_unixtime(unix_timestamp() , 'yyyy-MM-dd hh:mm:ss') as created_a

使用from_unixtime(unix_timestamp() , 'yyyy-MM-dd hh:mm:ss') 擷取系統時間，格式可以根據需要調整

4. 多個表進行join的時候，可能會報錯

使用set hive.auto.convert.join=false;解決

5. 建立表

create table if not exists brands (

  name string,

  created_at string,

  updated_at string

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

ESCAPED BY '\\'

STORED AS TEXTFILE;

以文本方式進行存儲，"\\"進行轉義，"\t"作為換行符

6.到處hive中的某個表中的資料到本地，執行hive指令如下：

hive 

-hiveconf local_path=/home/hive/hive_data/products_24_1 

-hiveconf hive_table=products_24_1 

-hiveconf columnstr=' name , created_at,  updated_at,   "released" as status ' 

-f /home/hive/export_hive_table_to_local.sql

需要執行的參數依次是

1.導出到本地的位置local_path

2.導出hive中的哪個表 hive_table

3. 導出products_24_1 表中的哪些字段 colunmstr

4. 根據上面的參數，在本地建立products_24_1 表，使用-f來指定調用的檔案

/home/hive/export_hive_table_to_local.sql 檔案内容如下：

insert overwrite local directory '${hiveconf:local_path}' 

STORED AS TEXTFILE

select ${hiveconf:columnstr}

from ${hiveconf:hive_table};

7.将本地檔案導入到psql資料庫中, hive對pg的支援不好，不能用sqoop來進行資料的導入，可以先将hive中的資料讀到本地，在使用python腳本來進行檔案的寫入

def insert_to_pg(conn , table_name , file_path , insert_columns=None):  

  conn = psycopg2.connect(conn)  

  cursor = conn.cursor()  

  if os.path.isfile( file_path ):  

    datafile=ReadFileProgress(file_path)  

    cursor.copy_from(file=datafile, table=table_name, sep='\t', null='\\N', size=81920, columns=insert_columns)  

    datafile.close()  

#!/usr/bin/python  

# #_*_ coding: utf-8 _*_  

import os , sys  

import psycopg2  

class ReadFileProgress:  

  def __init__(self, filename):  

    self.datafile = open(filename)  

    self.totalRecords = 0  

    self.totalBytes = os.stat(filename).st_size  

    self.readBytes = 0  

    self.datafile.readline()  

    i = 0  

    for i, l in enumerate(self.datafile):  

      pass  

    self.totalRecords = i + 1  

    sys.stderr.write("Number of records: %d\n" % (self.totalRecords))  

    self.datafile.seek(0)  

    self.perc5 = self.totalBytes / 20.0  

    self.perc5count = 0  

    self.lastPerc5 = 0  

    sys.stderr.write("Writing records: 0%")  

  def countBytes(self, size=0):  

    self.readBytes += size  

    if (self.readBytes - self.lastPerc5 >= self.perc5):  

      self.lastPerc5 = self.readBytes  

      if (int(self.readBytes / self.perc5) == 5):  

        sys.stderr.write("25%")  

      elif (int(self.readBytes / self.perc5) == 10):  

        sys.stderr.write("50%")  

      elif (int(self.readBytes / self.perc5) == 15):  

        sys.stderr.write("75%")  

      else:  

        sys.stderr.write(".")  

      sys.stderr.flush()  

  def readline(self, size=None):  

    countBytes(size)  

    return self.datafile.readline(size)  

  def read(self, size=None):  

    self.countBytes(size)  

    return self.datafile.read(size)  

  def close(self):  

    sys.stderr.write("100%\n")  

    self.datafile.close()  

8. 從pg上導出指定表

def do_export(conn , table_name , file_path , columns=None):  

  cursor.copy_to(file=file(file_path , 'w'), table=table_name, sep='\t', null='\\N', columns=columns)  

  cursor.close()  

  conn.commit()  

  sys.stdout.write("Transaction finished successfully.\n")  

9. 則select語句中也可以通過hiveconf來傳遞參數，執行hive指令

hive -hiveconf name='hello hive'

INSERT OVERWRITE TABLE companies

  '${hiveconf:name}' as name 

from companies_old

本文轉自 SimplePoint 51CTO部落格，原文連結：http://blog.51cto.com/2226894115/1898261，如需轉載請自行聯系原作者