[B! hive] monnalisasmileã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

monnalisasmile id:monnalisasmile

hiveã«é–¢ã™ã‚‹monnalisasmileã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (28)

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

${{author_name}}$
{{author_name}}{{created}}
{{ #comment }}{{ comment }}{{ /comment }}
- {{ label }}

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

Hadoopåˆ©ç”¨è€…ãªã‚‰ãã£ã¨çŸ¥ã£ã¦ã‚‹ã€Hive/Prestoã‚¯ã‚¨ãƒªé–¢æ•°ã®æŒ™å‹•ã®é•ã„ - Qiita
HiveQLã§ã¯ã‚¹ãƒ”ãƒ¼ãƒ‰ã«é›£ã‚’æ„Ÿã˜ã¦ã„ãŸãŸã‚ã€ç§ã‚‚Prestoã‚’ä½¿ã„å§‹ã‚ã¾ã—ãŸã€‚ MySQLã‚„Hiveã§ä½¿ã£ã¦ã„ãŸã‚¯ã‚¨ãƒªã‚’ç½®ãæ›ãˆã‚‹æ™‚ã«ãƒãƒžã£ãŸTipsã‚’ã¾ã¨ã‚ã¦ã„ãã¾ã™ã€‚ AWS Athenaã§Prestoã‚’ä½¿ã£ã¦ã„ã‚‹æ–¹ã‚‚å¢—ãˆã¦ã‚‹ã¨æ€ã†ã®ã§ã€Prestoæ¨™æº–é–¢æ•°ã§ã®è¨˜è¿°ä¾‹ã‚‚æ‹¡å……ã—ã¦ã„ãã¾ã™ã€‚ Prestoã¨ã¯ Prestoã¯ã‚ªãƒ³ãƒ¡ãƒ¢ãƒªã§å‹•ãåˆ†æ•£SQLã‚¨ãƒ³ã‚¸ãƒ³ã§ã€ãã®é€²åŒ–ã¯ç›®ã‚’è¦‹å¼µã‚‹ç‰©ã§ã™ã€‚ ç™ºè¡¨ã•ã‚ŒãŸå½“æ™‚ã¯è‰²ã€…ãªæˆç´„ãŒã‚ã‚Šä½¿ã†ã“ã¨ã‚’èºŠèº‡ã—ã¦ã„ã¾ã—ãŸãŒã€2015å¹´é ƒã‹ã‚‰ã¯ã‚‚ã†ä½¿ã‚ãªã„ç†ç”±ã¯ãªããªã‚Šã¾ã—ãŸã€‚ ã‚¢ãƒ‰ãƒ›ãƒƒã‚¯ã«ä½¿ãˆã‚‹ã¨ã¦ã‚‚é«˜é€ŸãªSQLã‚¨ãƒ³ã‚¸ãƒ³ã§ã™ã®ã§ã€ãƒãƒƒãƒå‘ã‘ã®Hiveã®ã‚ˆã†ã«å®Ÿè¡Œçµæžœã‚’å¾…ã¤æ™‚é–“ã¯ã»ã¨ã‚“ã©ã‚ã‚Šã¾ã›ã‚“ã€‚ Hiveã§ã™ã¨1ã¤1ã¤ã®å®Ÿè¡Œã«æ™‚é–“ãŒæŽ›ã‹ã‚‹ã®ã§ã€ã‚¯ã‚¨ãƒªã«æ…£ã‚Œã¦ã„ãªã„æ–°å‚è€…ã«ã¯è¾›ã„ç‰©ãŒã‚ã‚Šã¾ã—ãŸã€‚ ã—ã‹ã—Prestoã§ã¯ã‚¤ãƒ³ã‚¿ãƒ©ã‚¯ãƒ†ã‚£ãƒ–ã«å®Ÿè¡Œã§ãã¾ã™ã®ã§ã€ãƒˆãƒ©ã‚¤
monnalisasmile 2015/11/05
hadoop

Presto

MySQL

ã‚ã¨ã§èªã‚€

HIVE

SELECT

SQL
ãƒªãƒ³ã‚¯
crocos.jp
This domain may be for sale!
monnalisasmile 2014/11/21
Crocos

hadoop

hive

TreasureData

td

cocoiti

ãƒ“ãƒƒã‚°ãƒ‡ãƒ¼ã‚¿

td-agent

fluentd
ãƒªãƒ³ã‚¯
Hiveã‚¯ã‚¨ãƒªãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ãƒ¡ãƒ¢ - ç§‘å¦ã¨éžç§‘å¦ã®è¿·å®®
Hadoopæœ¬2ç‰ˆã‚’è²·ã£ãŸãã®ä¸€é€±é–“å¾Œã«Deals of the day ã§åŠé¡ã‚»ãƒ¼ãƒ«ã‚„ã‚‰ã‚Œã¦æ»ã«ãŸããªã£ãŸã®ã§è…¹ã„ã›ã«æ›¸ã ã¯ã˜ã‚ã« ã“ã“ã«æ›¸ã„ã¦ã‚ã‚‹ã®ã¯å…¨éƒ¨å‚è€ƒãƒªãƒ³ã‚¯ãƒ»æ–‡çŒ®ã‹ã‚‰ã²ã£ã±ã£ã¦ããŸã ã‘ã§ã€ã»ã¨ã‚“ã©å…¨éƒ¨æ¤œè¨¼ã—ã¦ãªã„ã€‚é–“é•ã„ãŒã‚ã‚Œã°ãªã‚‹ã¹ãæ—©ã‚ã«æ›´æ–°ã™ã‚‹ã‚ˆã†åŠªåŠ›ã¯ã™ã‚‹ãŒã€éµœå‘‘ã¿ã«ã—ã¦ä½•ãŒèµ·ãã¦ã‚‚è‡ªå·±è²¬ä»»ã§ã€‚ Hive ã®ã‚¯ã‚¨ãƒªãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã«é–¢ã™ã‚‹ãƒ¡ãƒ¢æ›¸ãã§ã‚ã‚‹ã€‚ä»¥ä¸‹ã®ã“ã¨ã¯ã€Œæ›¸ã„ã¦ã„ãªã„ã€ Hadoopè‡ªä½“ã®ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚° Hive ã®ã‚¯ã‚¨ãƒªãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ä»¥å¤–ã®è©± ä¾‹ãˆã°ã€åœ§ç¸®ãƒ•ã‚¡ã‚¤ãƒ«ã‚’ Hive ä¸Šã§æ‰±ã†ã«ã¯ã©ã†ã™ã‚‹ã‹ã¨ã‹ JOIN ä¸€ç•ªå·¦ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã«æœ€ã‚‚å¤§ããªãƒ†ãƒ¼ãƒ–ãƒ«ã‚’æŒã£ã¦ãã‚‹ ä¸€ç•ªå·¦ã®ãƒ†ãƒ¼ãƒ–ãƒ«ãŒMRã§ã„ã†å…¥åŠ›ãƒ‡ãƒ¼ã‚¿ã¨ã—ã¦æµã‚Œã‚‹ã€‚ã‚¤ãƒ³ãƒŠãƒ¼ãƒ†ãƒ¼ãƒ–ãƒ«ã®ãƒ‡ãƒ¼ã‚¿ã¯ãƒ¡ãƒ¢ãƒªã«ä¿æŒã•ã‚Œã‚‹ã€‚ åŒä¸€ JOIN ã‚ãƒ¼ é€šå¸¸ã¯ 1 JOIN = 1 MR ã‚¸ãƒ§ãƒ–ã ãŒã€åŒä¸€ã® JOIN ã‚ãƒ¼ã‚’ä½¿ã£ã¦ã„
monnalisasmile 2014/11/21
join

hive

map

Hadoop

database

mapreduce

HiveQL

!_Programing

ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°
ãƒªãƒ³ã‚¯
The Treasure Data Blog | How to Get More Clicks for Digital Advertising: Step by Step Guide to Optimizing CTRs with Real-time Data + Machine Learning
In the digital advertising space, optimizing the CTR (Click Through Rate) is one of the major challenges to increasing the performance of the advertising networks. Often times, machine learningÂ algorithms are used to optimize what ads are relevant to the incoming visitors by learning from the historical impression and click logs. However, collecting and running machine learning algorithms in real-
monnalisasmile 2014/10/14
learning

algorithms

Architecture

data

machine learning

how to

advertising

hive
ãƒªãƒ³ã‚¯
ã€ŽPrestoã¨ã¯ä½•ã‹ï¼ŒPrestoã§ä½•ãŒã§ãã‚‹ã‹ã€ - ãƒˆãƒ¬ã‚¸ãƒ£ãƒ¼ãƒ‡ãƒ¼ã‚¿ï¼ˆTreasure Dataï¼‰ãƒ–ãƒã‚°
ãƒˆãƒ¬ã‚¸ãƒ£ãƒ¼ãƒ‡ãƒ¼ã‚¿ã¯ã‚¯ãƒ©ã‚¦ãƒ‰ã§ãƒ‡ãƒ¼ã‚¿ãƒžãƒãƒ¼ã‚¸ãƒ¡ãƒ³ãƒˆã‚µãƒ¼ãƒ“ã‚¹ã‚’æä¾›ã—ã¦ã„ã¾ã™ã€‚ Hadoop Conference Japan 2014 ä»¥å‰ã«å‘ŠçŸ¥ã—ãŸHadoop Conference Japan 2014ã§ï¼Œå¼Šç¤¾Software Architectã®å¤æ©‹ãŒç™ºè¡¨ã—ã¾ã—ãŸã€‚ ãƒ†ãƒ¼ãƒžã¯ï¼ŒFacebookãŒå…¬é–‹ã—ãŸæ–°ã—ã„åˆ†æ•£å‡¦ç†åŸºç›¤ï¼ŒPrestoã€‚å®Ÿã¯FacebookãŒå½¼ã‚‰ã®è¶…å¤§è¦æ¨¡ãªãƒ‡ãƒ¼ã‚¿ã‚»ãƒƒãƒˆã«å¯¾ã—ã¦ã‚¤ãƒ³ã‚¿ãƒ©ã‚¯ãƒ†ã‚£ãƒ–ã«çµæžœã‚’è¿”ã›ã‚‹ã‚ˆã†ã«ã¨é–‹ç™ºã•ã‚ŒãŸã‚‚ã®ã§ã™ã€‚é–‹ç™ºãŒå§‹ã¾ã£ã¦ã¾ã 2å¹´ã‚‚çµŒã£ã¦ãŠã‚Šã¾ã›ã‚“ãŒï¼Œä»Šã§ã¯ãƒˆãƒ¬ã‚¸ãƒ£ãƒ¼ãƒ‡ãƒ¼ã‚¿ã‚’åˆã‚ã¨ã—ã¦å¤šãã®ãƒãƒƒã‚«ãƒ¼é”ãŒã‚³ãƒŸãƒƒã‚¿ãƒ¼ã¨ã—ã¦å‚åŠ ã™ã‚‹æ´»ç™ºçš„ãªãƒ—ãƒã‚¸ã‚§ã‚¯ãƒˆã«æˆé•·ã—ã¦ã„ã¾ã™ã€‚ Prestoã¯Hiveã‚„Impalaã¨åŒã˜ã€ŒSQL Query Engineã€ã§ã‚ã‚Šï¼Œç‰¹ã«æ•°ç™¾GBã‚’è¶…ãˆã‚‹å¤§è¦æ¨¡ãƒ‡ãƒ¼ã‚¿ã«å¯¾ã—ã¦ã‚‚ã‚¤ãƒ³ã‚¿ãƒ©ã‚¯ãƒ†ã‚£ãƒ–ãªãƒ¬ã‚¹ãƒãƒ³ã‚¹ã‚’ï¼ˆã‚³ãƒ³ãƒž0ç§’ä»¥ä¸‹ï¼Œé…ãã¦
monnalisasmile 2014/10/04
hive

Hadoop

facebook

Japan

impala

development

bigdata

presto

treasuredata
ãƒªãƒ³ã‚¯
Hiveã®joinã®æœ€é©åŒ– - wyukawa's diary
å…ƒãƒã‚¿ã¯ã“ã¡ã‚‰ Join Optimization in Apache Hive Hiveã¯0.7ã‹ã‚‰joinãŒæœ€é©åŒ–ã•ã‚Œã¦ã„ã¾ã™ã€‚ã©ã®ã‚ˆã†ã«æœ€é©åŒ–ã•ã‚ŒãŸã®ã‹ä¸Šè¨˜ã®è³‡æ–™ã‚’ã²ã‚‚ã¨ã„ã¦ã¿ã¾ã™ã€‚ ã„ã¾ã¾ã§ã®join ã„ã¾ã¾ã§ã®joinã¯ã„ã‚ã‚†ã‚‹ã‚½ãƒ¼ãƒˆãƒžãƒ¼ã‚¸ã‚¸ãƒ§ã‚¤ãƒ³ã§ã™ã€‚ mapãƒ•ã‚§ãƒ¼ã‚ºã§ãƒ†ãƒ¼ãƒ–ãƒ«ã®ãƒ‡ãƒ¼ã‚¿ã‚’èªã¿è¾¼ã‚“ã§joinã‚ãƒ¼ã€joinãƒãƒªãƒ¥ãƒ¼ã‚’å‡ºåŠ›ã—ã€shuffleãƒ•ã‚§ãƒ¼ã‚ºã§ã‚½ãƒ¼ãƒˆã€reduceãƒ•ã‚§ãƒ¼ã‚ºã§joinã¨ã„ã†æµã‚Œã§ã™ã€‚ ã“ã®å ´åˆshuffleãƒ•ã‚§ãƒ¼ã‚ºã®ã‚½ãƒ¼ãƒˆå‡¦ç†ãŒãƒœãƒˆãƒ«ãƒãƒƒã‚¯ã¨ãªã£ã¦ã„ã¾ã—ãŸã€‚ ãã“ã§ç™»å ´ã™ã‚‹ã®ãŒMap Joinã§ã™ã€‚ joinã®ç‰‡æ–¹ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã®ã‚µã‚¤ã‚ºãŒãƒ¡ãƒ¢ãƒªã«åŽã¾ã‚‹ã»ã©å°ã•ã„ã®ã§ã‚ã‚Œã°ã€mapperã®ãƒ¡ãƒ¢ãƒªã«èªã¿è¾¼ã‚“ã§mapãƒ•ã‚§ãƒ¼ã‚ºã ã‘ã§joinã—ã¾ã™ã€‚ ã“ã‚“ãªæ„Ÿã˜ã®æ§‹æ–‡ã§æ›¸ãã¾ã™ã€‚ select /*+map join(a)*/ * from src1 x join
monnalisasmile 2014/08/12
map

hive

hadoop

join
ãƒªãƒ³ã‚¯
SIOSã€€ãƒ“ãƒƒã‚°ãƒ‡ãƒ¼ã‚¿æŠ€è¡“ãƒ–ãƒã‚°: Treasure Dataã«ãŠã‘ã‚‹HiveQLã®Tips
ã“ã‚“ã«ã¡ã¯ã€ä»Šå›žã®ãƒ–ãƒã‚°æ‹…å½“ é«˜æ©‹ã§ã™ã€‚ æœ¬é¡Œã¨ã¯é€¸ã‚Œã¾ã™ãŒã€ãƒ“ãƒƒã‚°ãƒ‡ãƒ¼ã‚¿ã«é–¢é€£ã™ã‚‹ãƒˆãƒ¬ãƒ³ãƒ‰ã¨ã—ã¦ã€M2M(Machine to Machine)ã‚„IoT(Internet of Things)ã¨å‘¼ã°ã‚Œã‚‹æŠ€è¡“ãŒã‚ã‚Šã¾ã™ã€‚ SIOSãƒ“ãƒƒã‚°ãƒ‡ãƒ¼ã‚¿ãƒãƒ¼ãƒ ã¨ã—ã¦ã‚‚ã€ã“ã‚Œã‚‰ã®æŠ€è¡“ã«ã‚ˆã£ã¦å¤§é‡ã«åŽé›†ã•ã‚Œã‚‹ãƒ‡ãƒ¼ã‚¿ã«ã¯æ³¨ç›®ã—ã¦ã„ã¾ã™ã€‚ ã“ã‚Œã‚‰ã®æŠ€è¡“ã‚’å€‹äººã§å®Ÿç¾å¯èƒ½ãªãƒ—ãƒã‚°ãƒ©ãƒžãƒ–ãƒ«ãƒ‡ãƒã‚¤ã‚¹ã¨ã—ã¦ã€Arduinoã‚„Raspberry PiãŒæ™®åŠã—ã¦ãã¦ã„ã¾ã™ã€‚ ç‰¹ã«ã€Arduinoã¯ã€æŽ¥è§¦ã‚»ãƒ³ã‚µã‚„èµ¤å¤–ã‚»ãƒ³ã‚µãªã©å„ç¨®ã‚»ãƒ³ã‚µã‚’å®Ÿè£…ã§ãã€ãªãŠã‹ã¤Bluetoothã‚„ZigBeeãªã©ã®é€šä¿¡ãƒ¢ã‚¸ãƒ¥ãƒ¼ãƒ«ã®å®Ÿè£…ã‚‚å¯èƒ½ã§ã™ã€‚ ä¾‹ãˆã°ã€è¤‡æ•°å°ã®Arduinoã‚’çµ„ã¿åˆã‚ã›ã¦è‡ªå®…å†…ã‚»ãƒ³ã‚µãƒãƒƒãƒˆãƒ¯ãƒ¼ã‚¯ã‚’æ§‹ç¯‰ã—ã€æ—¥å¸¸ç”Ÿæ´»ã®è¦‹ãˆã‚‹åŒ–ãŒã§ããŸã‚‰æ¥½ã—ãã†ã§ã™ãã€‚ ã“ã†ã—ãŸãƒ“ãƒƒã‚°ãƒ‡ãƒ¼ã‚¿ã‚’ç”Ÿã¿å‡ºã™æ§˜ã€…ãªã‚¢ã‚¤ãƒ‡ã‚¢ã‚’å®Ÿç¾ã™ã‚‹ãŸã‚ã«ã€ç§ãŸã¡ã‚‚æ—¥ã€…ã€ãƒ“
monnalisasmile 2014/07/15
hiveql

hadoop

hive

treasure data
ãƒªãƒ³ã‚¯
Grokking the Menagerie: An Introduction to the Hadoop Software Ecosystem by Blake Matheny | Articles | ebooks for software developers, programmers and designers
monnalisasmile 2014/07/08
ã“ã‚ŒãŒä¸€ç•ªã‚ã‹ã‚Šã‚„ã™ã„

hadoop

software

data

IT

mapreduce

hbase

word

hive

server
ãƒªãƒ³ã‚¯
ãƒ‡ãƒ¼ã‚¿ã‚µã‚¤ã‚¨ãƒ³ãƒ†ã‚£ã‚¹ãƒˆã®ãŸã‚ã®HiveQLåˆ†æžã‚¯ã‚¨ãƒªãƒ†ãƒ³ãƒ—ãƒ¬ãƒ¼ãƒˆ ãã®ï¼– - ãƒˆãƒ¬ã‚¸ãƒ£ãƒ¼ãƒ‡ãƒ¼ã‚¿ï¼ˆTreasure Dataï¼‰ãƒ–ãƒã‚°
*ãƒˆãƒ¬ã‚¸ãƒ£ãƒ¼ãƒ‡ãƒ¼ã‚¿ã¯ãƒ‡ãƒ¼ã‚¿åŽé›†ã€ä¿å˜ã€åˆ†æžã®ãŸã‚ã®ã‚¨ãƒ³ãƒ‰ãƒ„ãƒ¼ã‚¨ãƒ³ãƒ‰ã§ã‚µãƒãƒ¼ãƒˆã•ã‚ŒãŸã‚¯ãƒ©ã‚¦ãƒ‰ã‚µãƒ¼ãƒ“ã‚¹ã§ã™ã€‚ ãƒ‡ãƒ¼ã‚¿ã‚µã‚¤ã‚¨ãƒ³ãƒ†ã‚£ã‚¹ãƒˆã®ãŸã‚ã®HiveQLåˆ†æžã‚¯ã‚¨ãƒªãƒ†ãƒ³ãƒ—ãƒ¬ãƒ¼ãƒˆã‚·ãƒªãƒ¼ã‚ºï¼š ãã®ï¼‘,Â ãã®ï¼’,Â ãã®ï¼“,Â ãã®ï¼”,Â ãã®ï¼•, ãã®ï¼– E. ãƒªãƒ†ãƒ³ã‚·ãƒ§ãƒ³åˆ†æž æœ€å¾Œã¯ãƒªãƒ†ãƒ³ã‚·ãƒ§ãƒ³ã«é–¢ã™ã‚‹ã‚¯ã‚¨ãƒªãƒ†ãƒ³ãƒ—ãƒ¬ãƒ¼ãƒˆã§ã™ã€‚ ãƒ‘ã‚¿ãƒ¼ãƒ³ E-1ï¼šç›´å¸°çŽ‡ â€» ã“ã“ã§ã¯åŒæ—¥å†…ã«1å›žã—ã‹ãƒã‚°ã‚¤ãƒ³ã—ãªã‹ã£ãŸãƒ¦ãƒ¼ã‚¶ãƒ¼ã‚’ã€Œç›´å¸°ã€ã¨è¦‹ãªã—ã¾ã™ã€‚ SELECT t2.d AS d, COUNT(1)/SUM(t2.cnt)*100 AS bounce_rate FROM ( SELECT TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS d, uid, COUNT(1) AS cnt FROM login GROUP BY TD_TIME_FORMAT(time, 'yyyy-M
monnalisasmile 2014/06/12
hiveql

analytics

hive

treasure data
ãƒªãƒ³ã‚¯
SQLæ„Ÿè¦šã§HiveQLã‚’æ›¸ãã¨ç—›ã„ç›®ã«ã‚ã†ä¾‹ - still deeper
Hadoop Advent Calendar 2013 4æ—¥ç›®ã®è¨˜äº‹ã§ã™ tl;dr explainã¨job historyã‚’èªã‚ 1 reducerã¯æ‚ª data skewã¯æ‚ª å‰æ›¸ã ã¿ã‚“ãªå¤§å¥½ãSQLã§Hadoopä¸Šã§ã®å‡¦ç†ã‚’å®Ÿè¡Œã§ãã‚‹Hiveã«ã¯ã¿ãªã•ã‚“æ™®æ®µã‹ã‚‰ãŠä¸–è©±ã«ãªã£ã¦ã„ã‚‹ã“ã¨ã§ã—ã‚‡ã†ã€‚ã¡ã‚‡ã£ã¨èª¿ã¹ç‰©ã§ã‚°ã‚°ã‚‹åº¦ã«ç›®ã«å…¥ã‚‹æ„›ã‚‰ã„ã—ã„ãƒžã‚¹ã‚³ãƒƒãƒˆãŒã€è’ã‚“ã å¿ƒã«æ¸…æ¶¼ãªé¢¨ã‚’ã¯ã“ã‚“ã§ãã‚Œã¾ã™ã€‚ ã§ã™ãŒHiveã®ã‚¯ã‚¨ãƒªè¨€èªžã¯SQLã§ã¯ãªãHiveQLã§ã™ã—ã€å®Ÿè¡Œã‚¨ãƒ³ã‚¸ãƒ³ã‚‚RDBã®ãã‚Œã¨ã¯å…¨ãç•°ãªã‚‹MapReduceã§ã™ã€‚SQLã®ã¤ã‚‚ã‚Šã§HiveQLã‚’æ›¸ã„ã¦ã„ã‚‹ã¨åœ°é›·ã‚’è¸ã‚“ã§ã—ã¾ã†ã“ã¨ãŒã¾ã‚Œã«ã‚ˆãã‚ã‚Šã¾ã™ã€‚æœ¬ã‚¨ãƒ³ãƒˆãƒªã§ã¯é™¥ã‚ŠãŒã¡ãªHiveQLã®è½ã¨ã—ç©´ã‚’2ã¤ç´¹ä»‹ã—ã¾ã™ã€‚ ä¾‹1 SELECT count(DISTINCT user_id) FROM access_log SQLã«æ…£ã‚ŒãŸæ–¹ã§ã‚ã‚Œ
monnalisasmile 2014/06/12
ã»ã»ã†ã»ã†ã»ã†

hiveql

Hadoop

data

hive

history

mapreduce

map

sql

tuning

performance
ãƒªãƒ³ã‚¯
SQLãƒ©ã‚¤ã‚¯ã«Hadoop Hiveã‚’ä½¿ã„å€’ã™!
ãƒ‘ãƒ¼ãƒ†ã‚£ã‚·ãƒ§ãƒ³ã‚’åˆ©ç”¨ã™ã‚‹ ä»Šå›žã¯å°‘ã—å‡ã£ãŸãƒ†ãƒ¼ãƒ–ãƒ«ã‚’å®šç¾©ã‚’ã—ã¦ã¿ã¾ã—ã‚‡ã†ã€‚ éƒµä¾¿ç•ªå·ãƒ‡ãƒ¼ã‚¿ã¯æ¯Žæœˆæ›´æ–°ã•ã‚Œã‚‹ã®ã§ã€ãƒ†ãƒ¼ãƒ–ãƒ«æŒ‡å®šæ™‚ã«ãƒãƒ¼ã‚¸ãƒ§ãƒ³ã‚‚æŒ‡å®šã§ãã‚‹ã‚ˆã†ã«ã—ã¾ã™ã€‚ã“ã®ã‚ˆã†ãªå ´åˆã€Hiveã§ã¯ãƒ‘ãƒ¼ãƒ†ã‚£ã‚·ãƒ§ãƒ³ã‚’ä½¿ã„ã¾ã™ã€‚ ä»¥ä¸‹ã«éƒµä¾¿ç•ªå·ã‚’ä¿å˜ã™ã‚‹ãƒ†ãƒ¼ãƒ–ãƒ«ã€Œzipã€ã‚’å®šç¾©ã—ã¾ã™ãŒã€æ—¥ä»˜åž‹DATEã®ãƒ‘ãƒ¼ãƒ†ã‚£ã‚·ãƒ§ãƒ³verã‚’è¨å®šã™ã‚‹ã‚ˆã†ã«ã—ã¾ã™ã€‚ hive> CREATE TABLE zip (zip STRING, pref INT, city STRING, town STRING) > PARTITIONED BY (ver DATE) > ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' > LINES TERMINATED BY '\n'; OK Time taken: 0.128 seconds
monnalisasmile 2013/10/08
hive

Hadoop

hbase

dev
ãƒªãƒ³ã‚¯
EMRã£ã¦ãªã‚“ã˜ã‚ƒï¼Ÿï¼ˆHiveã§S3ã®ãƒã‚°ã‚’JSTã«å¤‰æ›ã—ã¦1æ—¥åˆ†ã‚’ã¾ã¨ã‚ã‚‹ï¼‰
S3ã®webãƒ›ã‚¹ãƒ†ã‚£ãƒ³ã‚°ã§ã€ãƒã‚°å‡ºåŠ›ã®è¨å®šã‚’ã—ã¦ã„ãŸå ´åˆã€ãƒã‚°ãƒ•ã‚¡ã‚¤ãƒ«ãŒå¤§é‡ã«å‡ºåŠ›ã•ã‚Œã¾ã™ã€‚ ãƒã‚°ã®è¨˜éŒ²æ™‚é–“ã¯æ¨™æº–æ™‚ã§å‡ºåŠ›ã•ã‚Œã¦ã„ã¦ã‚ã‹ã‚Šã¥ã‚‰ã„ã§ã™ã€‚ ä»Šå›žã¯EMRã®Hiveã‚’åˆ©ç”¨ã—ã¦ã€æ—¥æœ¬æ™‚é–“ã®0æ™‚ã€œç¿Œæ—¥ã®0æ™‚ã¾ã§ã®ãƒã‚°ã‚’1ãƒ•ã‚¡ã‚¤ãƒ«ã«ã¾ã¨ã‚ã¦ã¿ãŸã„ã¨æ€ã„ã¾ã™ã€‚ HiveScript HiveScriptã¯ä»¥ä¸‹ã®ã‚ˆã†ã«ã—ã¾ã™ã€‚ ã¾ãšãƒ•ã‚©ãƒ¼ãƒžãƒƒãƒˆè§£æžç”¨ã®JARã‚’èªã¿è¾¼ã¿ã¾ã™ã€‚ add jar /home/hadoop/hive/contrib/hive-contrib-0.8.1.jar; å…¥åŠ›ç”¨ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’å®šç¾©ã—ã¾ã™ã€‚LOCATIONã¯å¼•æ•°ã‹ã‚‰å—ã‘å–ã‚Šã¾ã™ã€‚ ãƒãƒƒã‚¯ã¨ãªã‚‹ã®ã¯ã€S3ã®ãƒã‚°ã¯åŸºæœ¬çš„ã«åŠè§’ã‚¹ãƒšãƒ¼ã‚¹åŒºåˆ‡ã‚Šã§ã™ãŒã€é …ç›®ã®å€¤ã®ä¸ã«åŠè§’ã‚¹ãƒšãƒ¼ã‚¹ãŒå…¥ã‚Šè¾¼ã‚€ãŸã‚ã€ã€ŒFIELDS TERMINATED BYã€å¥ã§ã¯ãªãã€SERDEã‚’ä½¿ç”¨ã—ã¦æ£è¦è¡¨ç¾ã§ãƒã‚°ã®1è¡Œã‚’é …ç›®åˆ†ã‘ã—ã¾ã™ã€‚ ä»Šå›žã¯æ—¥ä»˜ãƒ•
monnalisasmile 2013/10/08
hive

s3

EMR
ãƒªãƒ³ã‚¯
Cloudera Managerã‚’ä½¿ã£ã¦ã€hadoopã€hiveç‰ã‚’EC2ã®è¤‡æ•°å°ã®ã‚µãƒ¼ãƒãƒ¼ã«ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã™ã‚‹æ–¹æ³•
Cloudera Managerã‚’ä½¿ã£ã¦ã€hadoopã€hiveç‰ã‚’EC2ã®è¤‡æ•°å°ã®ã‚µãƒ¼ãƒãƒ¼ã«ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã™ã‚‹æ–¹æ³•ã®è§£èª¬ã§ã™ã€‚ ã“ã®è¨˜äº‹ã®å¯¾è±¡è€…ã¯ã“ã‚“ãªäººãŸã¡ã§ã™ã€‚ Hadoopã‚„Hiveã‚’ãƒ¦ãƒ¼ã‚¶ãƒ¼ã¨ã—ã¦ä½¿ã„ãŸã„äºº è‡ªå‰ã®ã‚µãƒ¼ãƒãƒ¼ã¸å°Žå…¥ã™ã‚‹å‰ã«è©¦ã—ã«è§¦ã£ã¦ã¿ãŸã„äºº EC2ã¸ã®ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ãŒä½•æ•…ã‹å¤±æ•—ã™ã‚‹äºº(ãƒãƒžã‚Šãƒã‚¤ãƒ³ãƒˆãŒå¤šã„ã§ã™â€¦)
monnalisasmile 2013/09/04
hive

ec2

ec

Hadoop
ãƒªãƒ³ã‚¯
Apache Hive
Apache Hive The Apache Hive â„¢ is a distributed, fault-tolerant data warehouse system that enables analytics at a massive scale and facilitates reading, writing, and managing petabytes of data residing in distributed storage using SQL. Github Mail Docker Community Apache Hive is a distributed, fault-tolerant data warehouse system that enables analytics at a massive scale. Hive Metastore(HMS) provid
monnalisasmile 2013/09/04
project

hive

query

data

language

Hadoop

IT

analysis

SQL

*å‹‰å¼·
ãƒªãƒ³ã‚¯
jaws-users - EMR(Hive)åˆ©ç”¨æ™‚ã«ãŠã‘ã‚‹ã€S3ä¸Šãƒ•ã‚¡ã‚¤ãƒ«ã®LOADå‡¦ç†ã«ã¤ã„ã¦
monnalisasmile 2013/08/01
EMR

hive

aws

test

amazon

è§£æž

data

ã‚µãƒ¼ãƒ“ã‚¹

ãƒ‡ãƒ¼ã‚¿

as
ãƒªãƒ³ã‚¯
MySQLã‹ã‚‰ãƒ‡ãƒ¼ã‚¿ã‚’ãƒ€ãƒ³ãƒ—ã—ã¦Hiveã«ãƒ‡ãƒ¼ã‚¿ã‚’ãƒãƒ¼ãƒ‰ã™ã‚‹ã¾ã§ã®æµã‚Œ
é †ç•ªã«æ–¹æ³•ã‚’è¦‹ã¦ã„ãã¾ã™ã€‚ MySQLã‹ã‚‰æ•°ç™¾ä¸‡ãƒ¬ã‚³ãƒ¼ãƒ‰ç¨‹åº¦ã®ãƒ‡ãƒ¼ã‚¿ã‚’ãƒ€ãƒ³ãƒ—ã™ã‚‹ ãƒ‡ãƒ¼ã‚¿ãŒã›ã„ãœã„æ•°ç™¾ä¸‡ãƒ¬ã‚³ãƒ¼ãƒ‰ã—ã‹ãªã„ãªã‚‰ã©ã‚“ãªæ–¹æ³•ã§ã‚‚å¤§ä¸ˆå¤«ã§ã™ã€‚ä»£è¡¨çš„ãªæ–¹æ³•ã¯ä¸‹è¨˜ã®é€šã‚Šã§ã™ã€‚ mysql -e "SELECT ..." > file_name ã‚¯ã‚¨ãƒªã®çµæžœã‚’ãã®ã¾ã¾ãƒ•ã‚¡ã‚¤ãƒ«ã«ãƒªãƒ€ã‚¤ãƒ¬ã‚¯ãƒˆã™ã‚‹æ–¹æ³•ã§ã™ã€‚ãƒªãƒ¢ãƒ¼ãƒˆDBã«ã‚¯ãƒ©ã‚¤ã‚¢ãƒ³ãƒˆã‹ã‚‰ã¤ãªã„ã§ã„ã‚‹å ´åˆã«ä½¿ãˆã¾ã™ã€‚ SELECT ... INTO OUTFILE 'path/to/file.csv' myqlãŒç¨¼åƒã—ã¦ã„ã‚‹ã‚µãƒ¼ãƒãƒ¼ã®ãƒãƒ¼ã‚«ãƒ«FSä¸Šã«ã‚¯ã‚¨ãƒªã®çµæžœã‚’å‡ºåŠ›ã™ã‚‹æ–¹æ³•ã§ã™ã€‚åˆ¥é€”ã€NFSã‚„rsyncç‰ã§ãƒ‡ãƒ¼ã‚¿ã‚’è»¢é€ã™ã‚‹å¿…è¦ãŒã‚ã‚Šã¾ã™ã€‚ mysqlã‹ã‚‰ãƒ‡ãƒ¼ã‚¿ã‚’ãƒ€ãƒ³ãƒ—ã™ã‚‹ã€ã¨ã„ã†ç‚¹ã ã‘ã«é™ã‚Œã°ä¸Šè¨˜ã®æ–¹æ³•ã§ã„ã‘ã¾ã™ã€‚csvã«ã™ã‚‹ãŸã‚ã«ã‚³ãƒ³ãƒžã‚’å…¥ã‚ŒãŸã‚Šã€ãƒ•ã‚¡ã‚¤ãƒ«ã‚’åœ§ç¸®ã—ãŸã‚Šã™ã‚‹å¿…è¦ãŒã‚ã‚‹ãŸã‚ã€å®Ÿéš›ã«ã¯ã‚‚ã†ã¡ã‚‡ã£ã¨ç´°ã‹ã„æ‰‹é †ãŒå¿…è¦ã«ãªã‚Šã¾ã™ã€‚ I
monnalisasmile 2013/07/30
MySQL

hive

DB
ãƒªãƒ³ã‚¯
hiveã®ä¸€ç•ªã‚·ãƒ³ãƒ—ãƒ«ãªã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«æ–¹æ³•
hiveã‚’ç¾æ™‚ç‚¹ã§æœ€ã‚‚ç°¡å˜ã«ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã™ã‚‹æ‰‹é †ã®è§£èª¬ã§ã™ã€‚ Clouderaç¤¾ãŒå…¬é–‹ã—ã¦ã‚‹ãƒ‘ãƒƒã‚±ãƒ¼ã‚¸ã‚’ä½¿ã£ã¦ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã—ã¾ã™ã€‚ è³ªå•ã‚„é–“é•ã„æŒ‡æ‘˜ã¯ä½•ã§ã‚‚ã”é€£çµ¡ãã ã•ã„(^^) @ts_3156 hiveã¯hadoopã‚’ç°¡å˜ã«ä½¿ã†ãŸã‚ã®ãƒ„ãƒ¼ãƒ«ã§ã™ hiveã¨ã¯ã€hadoopã«ãŠã‘ã‚‹Map/Reduce(ä»¥ä¸‹ã€M/R)å‡¦ç†ã‚’HiveQL(hiveç‰ˆã®SQL)ã§æ›¸ãã“ã¨ãŒã§ãã‚‹ãƒ„ãƒ¼ãƒ«ã§ã™ã€‚ HiveQLã¯ã€å°‘ãªãã¨ã‚‚M/Rå‡¦ç†ã®ç›´æ›¸ãã‚ˆã‚Šã¯ã¨ã¦ã‚‚ç°¡å˜ã§ã™ã€‚ãªã®ã§ã€ç°¡å˜ã«hadoopã‚’ä½¿ã„ãŸã„äººã¯ã¾ãšhiveã‚’ä½¿ã£ã¦ã¿ã‚‹ã¨è‰¯ã„ã¨æ€ã„ã¾ã™ã€‚ ä¾‹ãˆã°ã€PV/UUã®é›†è¨ˆã¯ä»¥ä¸‹ã®ã‚¯ã‚¨ãƒªã ã‘ã§å‡ºæ¥ã¾ã™ã€‚ use [db_name]; SELECT url, -- é›†è¨ˆã—ãŸã„HTMLã®ãƒ‘ã‚¹ count(1) pv, count(distinct user_id) uu FROM [db_name].[ta
monnalisasmile 2013/07/30
hive

Hadoop

ec2
ãƒªãƒ³ã‚¯
Amazon Redshiftã®åˆ©ç‚¹
Amazon Redshiftã«ã¤ã„ã¦è‰²ã€…ã¨èžãæ©Ÿä¼šãŒã‚ã£ãŸã€‚ãã®æ™‚èžã„ãŸã“ã¨ãƒ¡ãƒ¢ã€‚ Amazon EMRã¨Amazon Redshiftã®é•ã„ ã¾ãšã¯ã€ã‚ˆãæ¯”è¼ƒã•ã‚Œã‚‹ã“ã¨ã«ãªã‚‹EMRã¨Redshiftã®é•ã„ã‹ã‚‰ã€‚ Amazon EMR Hadoopã‚¯ãƒ©ã‚¹ã‚¿ã¨Hiveã‚’ç°¡å˜ã«ä½¿ã†ãŸã‚ã®ã‚µãƒ¼ãƒ“ã‚¹ã€‚è‡ªç”±ãªå°æ•°ã®ã‚¯ãƒ©ã‚¹ã‚¿ã‚’è‡ªç”±ãªã‚¿ã‚¤ãƒŸãƒ³ã‚°ã§èµ·å‹•ã—ãŸã‚Šç ´æ£„ã—ãŸã‚Šã§ãã‚‹ã€‚ Hadoopã‚¯ãƒ©ã‚¹ã‚¿é‹ç”¨(åˆæœŸè¨å®šã€ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã€ç‰)ã®æ‰‹é–“ãŒå®Œå…¨ã«ä¸è¦ãªã®ã¯ã‚‚ã®ã™ã”ã„ãƒ¡ãƒªãƒƒãƒˆã€‚ ã‚¯ã‚¨ãƒªã®é€Ÿã•ã¯ã€ãƒã‚°ã®é‡ã«ã‚ˆã‚Šã¾ã™ãŒã€æ•°åˆ†ï½žæ•°ååˆ†ãã‚‰ã„ã‹ã‹ã‚‹ã€‚(ãƒã‚°ã®è¡Œæ•°ãŒæ•°ç™¾ä¸‡ï½žæ•°åƒä¸‡ã‚ã‚‹æ™‚) åˆ©ç”¨è€…ã‹ã‚‰è¦‹ãŸåŸºæœ¬çš„ãªç”¨é€”ãƒ»ã§ãã‚‹ã“ã¨ã¯EMRã¨ã»ã¨ã‚“ã©åŒã˜ã€‚ãŸã ã€ä»•çµ„ã¿ãŒå…¨ãé•ã†ã€‚ Redshiftã¯RDBã®ã‚ˆã†ãª(Postgresãƒ™ãƒ¼ã‚¹ã‚‰ã—ã„)ãƒ†ãƒ¼ãƒ–ãƒ«è¨è¨ˆã‚’æŒã¤ã€‚ä¾‹ãˆã°ã€VARCHAR(255)ã¿ãŸã„ãªã‚«ãƒ©ãƒ ã‚’æŒã¤ã€‚EMR
monnalisasmile 2013/07/30
EMR

Redshift

hive
ãƒªãƒ³ã‚¯
(ç¿»è¨³)ã‚¨ãƒ³ã‚²ãƒ¼ã‚¸ãƒ¡ãƒ³ãƒˆã®é«˜ã„ãƒ¦ãƒ¼ã‚¶ãƒ¼ã‚’å¢—ã‚„ã™ãŸã‚ã®Growth HackingæŒ‡æ¨™
monnalisasmile 2013/07/30
growth

æŒ‡æ¨™

hive

Facebook

Twitter

é‡è¦

ã‚³ãƒ³ãƒ†ãƒ³ãƒ„

zynga

dropbox

äºº
ãƒªãƒ³ã‚¯
cloudpackãƒ–ãƒã‚° - EMRã£ã¦ãªã‚“ã˜ã‚ƒï¼Ÿï¼ˆHiveã®DynamicPartitionã§ãƒ•ã‚¡ã‚¤ãƒ«ã‚’åˆ†å‰²ã—ã¦S3ã«å‡ºåŠ›ï¼‰
ã‚ã‚‹æ•°å¹´åˆ†ã®ãƒ‡ãƒ¼ã‚¿ãŒS3ä¸Šã«1ã¤ã®ãƒ•ã‚¡ã‚¤ãƒ«ã¨ã—ã¦æ ¼ç´ã•ã‚Œã¦ã„ã‚‹å ´åˆã«ã€ EMRã§å¹´ã‚„æœˆå˜ä½ã§åˆ†å‰²ã—ãŸã„ã¨ã„ã†è¦æœ›ãŒä¸€éƒ¨ã§ã‚ã£ãŸãŸã‚ã€ã‚„ã£ã¦ã¿ã¾ã—ãŸã€‚ ä¾‹ã¨ã—ã¦ã€ä»¥ä¸‹ã®ã‚ˆã†ãªCSVãŒã‚ã‚‹ã¨ã—ã¾ã™ã€‚ â—‹test.csv 2012,01,12,title1,body1 2012,01,22,title2,body2 2012,02,02,title3,body3 2012,03,01,title4,body4 2012,03,11,title5,body5 2012,03,18,title6,body6 2012,04,04,title7,body7 2012,05,02,title8,body8 2012,05,05,title9,body9 2012,06,13,title10,body10 2012,06,14,title11,body11 2012,06,29,title12,body1
monnalisasmile 2013/07/29
EMR

hive

aws

Amazon EC2

ã‚µãƒ¼ãƒ

ã‚¯ãƒ©ã‚¦ãƒ‰ãƒ‘ãƒƒã‚¯

CloudFront

ã‚¯ãƒ©ã‚¦ãƒ‰

Amazon S3

cloudpack
ãƒªãƒ³ã‚¯
1 2 æ¬¡ã®ãƒšãƒ¼ã‚¸