Hadoop Scribe nginxphp Data Warehousing at Facebook Web

暴风Hadoop集群架构流程 Scribe nginx+php

Data Warehousing at Facebook Web Servers Scribe Servers Filers Oracle RAC Hive on Hadoop Cluster Federated My. SQL

What is Hive

Installation and Configuration • 安装和配置hadoop集群 • 下� hive 安装包 • 解� 至安装目� • 配置HADOOP_HOME和JAVA_HOME� 量 • 修改存� 元数据的数据� （derby、mysql、 oracle）

Installation and Configuration • • hive 的元数据存�

DDL and DML Operations • • hive建表� 法格式

DDL and DML Operations • • hive建表� 句 CREATE TABLE page_view (view. Time INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User') COMMENT 'This is the page view table' PARTITIONED BY(dt STRING, country STRING) CLUSTERED BY(userid) SORTED BY(view. Time) INTO 32 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY '01' COLLECTION ITEMS TERMINATED BY '02' MAP KEYS TERMINATED BY '03' STORED AS SEQUENCEFILE;

DDL and DML Operations • • 修改表 增加分区 ALTER TABLE page_view ADD PARTITION (dt='2008 -08 -08', country='us') location '/path/to/us/part 080808' PARTITION (dt='2008 -08 -09', country='us') location '/path/to/us/part 080809'; • 修改表 � 除分区 ALTER TABLE page_view DROP PARTITION (dt='2008 -08 -08', country='us'); • 修改表 重命名表 ALTER TABLE table_name RENAME TO new_table_name • 修改表 修改字段 ALTER TABLE test_change CHANGE a a 1 STRING AFTER b;

DDL and DML Operations • • 加� 数据 LOAD DATA INPATH '/user/myname/kv 2. txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008 -08 -15'); 加载（本地、hdfs）文件到指定的表 分区 FROM src INSERT OVERWRITE TABLE dest 1 SELECT src. * WHERE src. key < 100 INSERT OVERWRITE TABLE dest 2 SELECT src. key, src. value WHERE src. key >= 100 and src. key < 200 从指定表中选取数据插入到其他表中

HQL use case • • select � 法� 构 SELECT [ALL | DISTINCT] select_expr, . . . FROM table_reference [WHERE where_condition] [GROUP BY col_list] [ CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ] [LIMIT number]

HQL use case • • select 案例 SELECT pv. pageid, u. age FROM page_view p JOIN user u ON (pv. userid = u. userid) JOIN newuser x on (u. age = x. age);

使用UDF • add jar /tmp/hello. UDF. jar; • create temporary function helloworld as 'com. hrj. hive. udf. Hello. UDF'; • select helloworld(t. col 1) from t limit 10; • drop temporary function helloworld;

Hive Optimization • join� 化 Mapjoin 的限制是无法� 行a FULL/RIGHT OUTER JOIN b，和map join相关的hive参数： hive. join. emit. interval hive. mapjoin. size. key hive. mapjoin. cache. numrows 由于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' 最好修改� ： 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操作的每一个mapred程序中，hive都会把出� 在join� 句中相� 靠后的表的数据 stream化，相� 靠前的� 的数据� 存在内存中。当然，也可以手� 指定stream化的表： SELECT /*+ STREAMTABLE(a) */ a. val, b. val, c. val FROM a JOIN b ON (a. key = b. key 1) JOI N c ON (c. key = b. key 1)

Hive Optimization • • join� 化 Mapjoin 的限制是无法� 行a FULL/RIGHT OUTER JOIN b，和map join相关的hive参数： hive. join. emit. interval hive. mapjoin. size. key hive. mapjoin. cache. numrows 由于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' 最好修改� ： 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操作的每一个mapred程序中，hive都会把出� 在join� 句中相� 靠后的表的数据 stream化，相� 靠前的� 的数据� 存在内存中。当然，也可以手� 指定stream化的表： SELECT /*+ STREAMTABLE(a) */ a. val, b. val, c. val FROM a JOIN b ON (a. key = b. key 1) JOI N c ON (c. key = b. key 1)

创建 LZO 压缩表 • • • CREATE EXTERNAL TABLE foo ( column. A string, column. B string ) PARTITIONED BY (date string) ROW FORMAT DELIMITED FIELDS TERMINATED BY "t" STORED AS INPUTFORMAT "com. hadoop. mapred. Deprecated. Lzo. Text. Input. Format" • OUTPUTFORMAT "org. apache. hadoop. hive. ql. io. Hive. Ignore. Key. Text. Output. Format" • LOCATION '/data/dw/uid/';

Hive Lzo 文件加载流程 • 压缩 • lzop uid. txt • 上传 • hadoop fs -put uid. txt. lzo /data/dw/uid/ • 创建索引 • hadoop jar /path/to/your/hadoop-lzo. jar com. hadoop. compression. lzo. Distributed. Lzo. Indexer /data/dw/uid. txt. lzo

生成 1000 w ids • hadoop jar contrib/streaming/hadoopstreaming-1. 0. 3. jar • -mapper 'seq 100000' • -num. Reduce. Tasks 0 • -input /user/hadoop/Pi. Estimator_TMP_3_141592 654/in/ • -output • /opt/ids/

生成r. pl代码 • #! /usr/bin/perl -X • $i=0; • • • while($i<=10000){ $i++; print `< /dev/urandom tr -dc _A-Z-a-z-0 -9 |

• hadoop jar contrib/streaming/hadoopstreaming-1. 0. 3. jar -mapper 'cat' reducer 'seq 10000' num. Reduce. Tasks 10 -input /data/ -output /data/test 3/

1. seq 10 > text 1. hadoop fs -put test /data/text 2. hadoop jar contrib/streaming/hadoopstreaming-1. 0. 3. jar -mapper 'cat' -reducer 'seq 10000' -num. Reduce. Tasks 10 -input /data/text -output /data/test 3/ 3. CREATE EXTERNAL TABLE test 3 (text string) LOCATION '/data/test 3/'

Hive vs sql •

生成 100亿车牌号 • hadoop jar hadoop-examples-1. 0. 3. jar pi 10000 • 生成 100个文件 • hadoop jar contrib/streaming/hadoopstreaming-1. 0. 3. jar -num. Reduce. Tasks 0 file rand. pl -mapper rand. pl -input /user/root/Pi. Estimator_TMP_3_141592654 /in -output /opt/chepai/

通过Reducer 生成 100 w 测试数据 • seq 10 > abc. txt • hadoop fs -put abc. txt /data/ • hadoop jar contrib/streaming/hadoopstreaming-1. 0. 3. jar -num. Reduce. Tasks 10 mapper 'cat' -reducer 'seq 100000' -input /data/abc. txt -output /opt/chepai/

配置队列运行 • set mapred. job. queue. name=hive • select * from uid;

Hadoop集群汇总监控 Ganglia Page � 47

HAProxy+Hive 网络拓扑 Queries HAProxy Hive Hadoop Hive

HAProxy+Hive高可用集群

php. Hive. Admin 界面

材料: http: //trac. nchc. org. tw/cloud/ http: //www. cloudera. com/downloads/sqoop/ 材料 查询界面: http: //www. phphiveadmin. net 任务调度 • http: //incubator. apache. org/oozie/ • http: //www. cronhub. com/ • http: //sna-projects. com/azkaban/screenshots. php 数据交换 http: //code. taobao. org/p/datax/wiki/Data. X%E 4%BA%A 7%E 5%93%81%E 8%AF%B 4%E 6 %98%8 E/ https: //github. com/zhuyeqing/Com. ETL http: //sqoop. apache. org/ http: //archive. cloudera. com/cdh/3/sqoop/Sqoop. User. Guide. html hadoop windows 版本 http: //trac. nchc. org. tw/cloud/wiki/Hadoop 4 Win

Pig System parsed program Parser user Pig Latin program Pig Compiler execution plan map-red. jobs cross-job optimizer output f( ) MR Compiler join Map-Reduce filter Cluster X Y

Introduction to Pig 操作实例 Users = LOAD ‘users. in’ AS (name, age); Fltrd = FILTER Users by age >= 18 and age <= 25; Pages = LOAD ‘pages. in’ AS (user, url); Jnd = JOIN Fltrd BY name, Pages BY user; Grpd = GROUP Jnd by url; Smmd = FOREACH Grpd GENERATE group, COUNT(Jnd) AS clicks; Srtd = ORDER Smmd BY clicks; Top 100 = LIMIT Srtd 100; STORE Top 100 INTO ‘top 100 sites. out’;

Sqoop最佳实践 • HDFS到DB sqoop export --connect jdbc: oracle: thin: @XXX: 1521: XXX --username XXXX --password XXXXX --export-dir /user/flume/part-r-00000 -m 1 --table NS_SLLOG_CLIENT_SUM_OS --update-key ACTIVITYTIME, CLIENT_VER, OS --update-mode allowinsert --input-fields-terminated-by 't' --outdir jobs --package-name com. uusee. sqoop 第六次技术聚会分享

日志收集——flume. NG 1）它是Flume. OG的重构版本，apache来 开发管理。 2）它变的很简单， Master、zookeeper、 collector和Web. UI没有了 3）简化成了 1、source （avro：很简单使用；exec： 使用shell命令） 2、sink （hdfs、file） 3、channel(Memory、disk)

Flume. NG——收集tomcat日志 例子场景描述：将tomcat的日志收集到指定的目录，tomcat 安装在/opt/tomcat, 日志存放在 var/log/data agent 1. sources = source 1 agent 1. sinks = sink 1 agent 1. channels = channel 1 #Describe/configure source 1 agent 1. sources. source 1. type = exec agent 1. sources. source 1. command = tail -n +0 -F /opt/tomcat/logs/ catalina. out agent 1. sources. source 1. channels = channel 1 # Describe sink 1 agent 1. sinks. sink 1. type = file_roll agent 1. sinks. sink 1. sink. directory=/var/log/data # Use a channel which buffers events in memory agent 1. channels. channel 1. type = file agent 1. channels. channel 1. checkpoint. Dir=/var/checkpoint agent 1. channels. channel 1. data. Dirs=/var/tmp agent 1. channels. channel 1. capacity = 1000 agent 1. channels. channel 1. transaction. Capactiy = 100 # Bind the source and sink to the channel agent 1. sources. source 1. channels = channel 1 agent 1. sinks. sink 1. channel = channel 1 运行agent 1 bin/flume-ng agent --conf-file tomcat. conf --name agent 1 -Dflume. root. logger=INFO, console agent自动会将日志收集到指定的var/log/data

Q&A