數(shù)據(jù)遷移心得

  前幾天出差，去客戶(hù)現(xiàn)場(chǎng)幫忙遷移數(shù)據(jù)，經(jīng)過(guò)幾天的奮戰(zhàn)，終于將遷移數(shù)據(jù)自動(dòng)化起來(lái)，并且可以日跑批操作，這里小編就跟大家分享下，這其中踩過(guò)的坑（也可能是實(shí)戰(zhàn)經(jīng)驗(yàn)不豐富導(dǎo)致）。
  首先，榮小編我抱怨一下，不是自己熟悉的開(kāi)發(fā)環(huán)境真的有些難過(guò)，給一臺(tái)電腦，咱不說(shuō)沒(méi)有IDE，就連java都沒(méi)有安裝，連接數(shù)據(jù)庫(kù)的工具也沒(méi)有，唯一值得慶幸的是有xshell，但是完全不符合個(gè)人快捷鍵的喜好，沒(méi)辦法，想要開(kāi)發(fā)高效，自己動(dòng)手配置吧。單單是配置這些開(kāi)發(fā)環(huán)境就整整犧牲了小編一上午的時(shí)間，還好后期開(kāi)發(fā)有明顯的提速。中午吃個(gè)飯，下午進(jìn)入正題。
  下午拿到遷移任務(wù)，發(fā)現(xiàn)一個(gè)庫(kù)中有很多表，其中表有大有小，當(dāng)時(shí)我沉默了幾秒鐘，感覺(jué)這個(gè)星期是交代在這里了。這里小編在那幾秒鐘的沉默中也把這個(gè)遷移流程想了一下，首先數(shù)據(jù)是存在關(guān)系型數(shù)據(jù)庫(kù)中的，然后我們要通過(guò)sqoop將數(shù)據(jù)上傳到HDFS中，然后用hive的外表去映射這些數(shù)據(jù)，最終建立有索引的內(nèi)表來(lái)存儲(chǔ)一份完整的數(shù)據(jù)。內(nèi)表一般都是分區(qū)，分桶，且有索引的orc表，查詢(xún)速度明顯比外表快很多。那么接下來(lái)小編就將這其中的步驟，一點(diǎn)點(diǎn)的分析下。

前期準(zhǔn)備：

   拿到一個(gè)庫(kù)的數(shù)據(jù)時(shí)，我們首先分析下這里有哪些表比較大，哪些表比較小，將大表和小表分開(kāi)，使用不同的遷移方法，一般都是客戶(hù)提供每張表的數(shù)據(jù)條數(shù)，如果沒(méi)有的話，只能selecct count(*) from table; 將這些表的數(shù)據(jù)查出來(lái)，不僅便于區(qū)分大小表，而且對(duì)后期數(shù)據(jù)核對(duì)有較大的幫助。

1. 從關(guān)系型數(shù)據(jù)庫(kù)將數(shù)據(jù)遷移到HDFS中

  這里遷移數(shù)據(jù)小編是用的sqoop，雖然sqoop比較慢，但是學(xué)習(xí)成本相對(duì)較低，而且便于批量的生成語(yǔ)句，對(duì)開(kāi)發(fā)要求沒(méi)那么高。首先先測(cè)試一個(gè)sqoop是否可以成功的遷移數(shù)據(jù)，然后編寫(xiě)腳本批量的生成sqoop語(yǔ)句，最后調(diào)用這些語(yǔ)句，后臺(tái)并行的遷移數(shù)據(jù)。這里小編先說(shuō)說(shuō)使用sqoop的幾個(gè)小竅門(mén)：

• 如果集群的資源比較充分，一般新集群是沒(méi)有什么生產(chǎn)任務(wù)的，我們的sqoop語(yǔ)句中可以加入：

  -m 這個(gè)參數(shù)可以設(shè)置為>1 ，表示并行多個(gè)map去抽取數(shù)據(jù)。
  --split-by 當(dāng)然-m 參數(shù)設(shè)置大于后，要同時(shí)設(shè)置這個(gè)參數(shù)，表示以表中的哪一個(gè)字段去分map并行。

  這里選取--split-by 盡量使用表中比較分散的字段，保證每一個(gè)map任務(wù)抽取的數(shù)據(jù)量都大致相同。

• 如果表的數(shù)據(jù)量比較大，比如超過(guò)億條，我們這里就需要將這個(gè)表分成多個(gè)sqoop去抽?。?/p>

  --query : 指定where后的條件，抽取部分?jǐn)?shù)據(jù)
     這樣的好處是：如果只有一個(gè)sqoop任務(wù)，抽取了90%的數(shù)據(jù)后，發(fā)現(xiàn)sqoop任務(wù)掛了，那么本次抽取失敗，不僅耗時(shí)，而且數(shù)據(jù)沒(méi)有抽取到。分多個(gè)sqoop任務(wù)，不僅可并行，而且每個(gè)任務(wù)的數(shù)據(jù)量也不大，如果有任務(wù)掛了，只需要抽象抽取那個(gè)where條件下的數(shù)據(jù)即，并且對(duì)于找錯(cuò)也有極大的幫助。
     分區(qū)字段的選取也同樣重要，這里一般都是使用日期作為where的后的條件，保證每個(gè)sqoop任務(wù)分的的數(shù)據(jù)量相差無(wú)幾。

  • sqoop抽取數(shù)據(jù)的目錄規(guī)劃

    #小表目錄規(guī)劃
    /tmp/庫(kù)名/表名
    #大表的目錄規(guī)劃
    /tmp/庫(kù)名/表名/分區(qū)名

• query語(yǔ)句：在sqoop命令中，我們編寫(xiě)查詢(xún)語(yǔ)句去抽取數(shù)據(jù)時(shí)，切記不要：

  -- ×
  select * from table;
  -- √
  select 字段1,字段2.... from table;
  不然可能會(huì)導(dǎo)致sqoop抽取速度變慢，甚至可能導(dǎo)致沒(méi)有抽取到數(shù)據(jù)。
     當(dāng)我們注意了以上的內(nèi)容后，就可以編寫(xiě)腳本批量的生成每張表的sqoop語(yǔ)句了，根據(jù)庫(kù)名.表名，獲取關(guān)系型數(shù)據(jù)庫(kù)中表的元數(shù)據(jù)，最后將sqoop組裝起來(lái)。最后在編寫(xiě)任務(wù)腳本，定時(shí)執(zhí)行這些sqoop語(yǔ)句。
  實(shí)際數(shù)據(jù)分享：
     這里小編測(cè)試過(guò)，數(shù)據(jù)量比較大時(shí)，多sqoop和單sqoop的耗時(shí)：
  以600G數(shù)據(jù)為例：
     - 多sqoop 并行抽取數(shù)據(jù)耗時(shí)：3~4小時(shí)。
     - 單sqoop 抽取數(shù)據(jù)耗時(shí)：12小時(shí)以上。
     - 單sqoop && （-m 1）抽取5千萬(wàn)條數(shù)據(jù)，大概是27分鐘。

2. 建立外表映射

   說(shuō)白了就是將抽取到的數(shù)據(jù)，在hive中通過(guò)外表的方式映射出來(lái)，其實(shí)這里沒(méi)什么難的，主要是看客戶(hù)如果要求，可能是外表單獨(dú)一個(gè)庫(kù)，或者外表的名稱(chēng)統(tǒng)一是：表名_ext。但是切記，不要手動(dòng)的去編寫(xiě)建表語(yǔ)句，如果表有百?gòu)堃陨?，心態(tài)容易炸，這里可以使用關(guān)系型數(shù)據(jù)庫(kù)的元數(shù)據(jù) ，生成hive的建表語(yǔ)句的，這里我們與MySQL為例：

SELECT CONCAT('create table ', TABLE_NAME, '(', substring(column_info, 1, length(column_info) - 1), ')', ' comment ', '"', TABLE_COMMENT, '"', ';')
FROM (SELECT TABLE_NAME, TABLE_COMMENT, group_concat(CONCAT(COLUMN_NAME, ' ', DATA_TYPE, ' comment ', '"', COLUMN_COMMENT, '"')) AS column_info
FROM (SELECT t1.TABLE_NAME, CASE WHEN t2.TABLE_COMMENT = NULL THEN t1.TABLE_NAME ELSE t2.TABLE_COMMENT END AS TABLE_COMMENT, COLUMN_NAME, CASE WHEN DATA_TYPE = 'varchar' THEN 'string' WHEN DATA_TYPE = 'int' THEN 'int' WHEN DATA_TYPE = 'tinyint' THEN 'tinyint' WHEN DATA_TYPE = 'decimal' THEN 'double' WHEN DATA_TYPE = 'datetime' THEN 'string' WHEN DATA_TYPE = 'timestamp' THEN 'string' WHEN DATA_TYPE = 'float' THEN 'double' WHEN DATA_TYPE = 'double' THEN 'double' WHEN DATA_TYPE = 'bigint' THEN 'bigint' END AS DATA_TYPE, CASE WHEN COLUMN_COMMENT = NULL THEN COLUMN_NAME ELSE COLUMN_COMMENT END AS COLUMN_COMMENT
FROM COLUMNS t1 JOIN TABLES t2 ON t1.TABLE_NAME = t2.TABLE_NAME
WHERE t1.TABLE_NAME = 't_app_equipment_status'
) t3
GROUP BY TABLE_NAME, TABLE_COMMENT
) t4;

網(wǎng)上這樣的例子很多，這里小編就不在介紹。當(dāng)外表建立好之后，最好核對(duì)一下數(shù)據(jù)量的大小，對(duì)比下關(guān)系型數(shù)據(jù)庫(kù)中表的數(shù)據(jù)和hive中的數(shù)據(jù)是否相同，這樣驗(yàn)證了sqoop這一環(huán)節(jié)是否有數(shù)據(jù)丟失的情況。
遇到的坑：
  當(dāng)大表我們?cè)诜謪^(qū)抽取時(shí)，是無(wú)法直接映射成為外表的，我們需要建立范圍分區(qū)表，將表的分區(qū)目錄映射到各個(gè)分區(qū)上。

3. 建立高效的內(nèi)表

   其實(shí)這一步就是將，外表的數(shù)據(jù)，insert到一張和外表字段相同的經(jīng)過(guò)優(yōu)化的內(nèi)表中，這張內(nèi)表一般都是分區(qū)分桶，建立索引，或者基于閃存的表，反正就是查詢(xún)的速度大大提高的一張表，也叫做業(yè)務(wù)表。
   小編這里用的是一種基于閃存的高效查詢(xún)的，企業(yè)內(nèi)部開(kāi)發(fā)的一種表結(jié)構(gòu)。小編這里介紹一下如何確定分桶字段：分桶的好處是
   （1）獲得更高的查詢(xún)處理效率。桶為表加上了額外的結(jié)構(gòu)，Hive 在處理有些查詢(xún)時(shí)能利用這個(gè)結(jié)構(gòu)。具體而言，連接兩個(gè)在（包含連接列的）相同列上劃分了桶的表，可以使用 Map 端連接 （Map-side join）高效的實(shí)現(xiàn)。比如JOIN操作。對(duì)于JOIN操作兩個(gè)表有一個(gè)相同的列，如果對(duì)這兩個(gè)表都進(jìn)行了桶操作。那么將保存相同列值的桶進(jìn)行JOIN操作就可以，可以大大較少JOIN的數(shù)據(jù)量。
   （2）使取樣（sampling）更高效。在處理大規(guī)模數(shù)據(jù)集時(shí)，在開(kāi)發(fā)和修改查詢(xún)的階段，如果能在數(shù)據(jù)集的一小部分?jǐn)?shù)據(jù)上試運(yùn)行查詢(xún)，會(huì)帶來(lái)很多方便。
   那么如果確定分桶字段呢，一般的如果有主鍵的表就使用主鍵作為分桶字段，如果沒(méi)有主鍵的表，找?guī)讉€(gè)比較分散的字段使用：

 select count(distinct feild) from table;

找出數(shù)據(jù)最大的那個(gè)字段作為分桶字段。具體的分桶數(shù)，這里建議是一個(gè)質(zhì)數(shù)，因?yàn)槿绻且粋€(gè)非質(zhì)數(shù)，那么可能導(dǎo)致分桶不均勻，因?yàn)槿绻滞皵?shù)是9的話，那么字段值如果為18、27都會(huì)分到一個(gè)桶中，可能會(huì)導(dǎo)致桶“熱點(diǎn)”。

4. 外表insert 到 內(nèi)表中

   這個(gè)過(guò)程是耗時(shí)僅次于sqoop的過(guò)程，由于我們的內(nèi)表建立的分區(qū)，那么在這個(gè)步驟中我們需要使用hive的動(dòng)態(tài)分區(qū)插入，插入語(yǔ)句一般都是：

    insert into table_1 partition(par_field) select field1.field2...from table_2 ;

這里需要注意的是select 最后一個(gè)字段一定要是分區(qū)字段。
   當(dāng)我們insert 后，需要核對(duì)下是否所有的數(shù)據(jù)全部insert成功，此時(shí)：外表數(shù)據(jù)量=內(nèi)表數(shù)據(jù)量=關(guān)系型數(shù)據(jù)庫(kù)數(shù)據(jù)量。

5. 日增量數(shù)據(jù)的處理

   當(dāng)我們完成數(shù)據(jù)遷移后，其實(shí)外表相當(dāng)于一個(gè)中轉(zhuǎn)站，僅僅是將數(shù)據(jù)中轉(zhuǎn)到內(nèi)表中，如果我們確保了內(nèi)表中有一份完整的數(shù)據(jù)，此時(shí)可以將外表的數(shù)據(jù)清空，這也是為什么我們將外表的location 設(shè)置為/tmp下 的原因，清空外表數(shù)據(jù)后，將日增數(shù)據(jù)抽取到外表的location地址上，然后全量的將外表數(shù)據(jù)insert到內(nèi)表中，就保證了日增量數(shù)據(jù)的成功導(dǎo)入到hive。