You are browsing a read-only backup copy of Wikitech. The primary site can be found at wikitech.wikimedia.org

Analytics/Systems/Cluster/Hive/Queries: Difference between revisions

From Wikitech-static
< Analytics‎ | Systems‎ | Cluster‎ | Hive
Jump to navigation Jump to search
imported>Neil P. Quinn-WMF
(Add advice on counting rows which match a criterion)
imported>Btullis
 
(14 intermediate revisions by 11 users not shown)
Line 1: Line 1:
== Running queries ==
#REDIRECT [[Data Engineering/Systems/Cluster/Hive/Queries]]
 
Note: The Hive client that is launched by using <code>hive</code>cli, is being deprecated, and is being replaced with <code>beeline</code>interface for HiveServer2 instead. Querying works the same way as this tutorial will explain, but you can use <code>beeline</code> in place of <code>hive</code>. See [[Analytics/Cluster/Beeline]] for full usage documentation.
 
Once you can <code>ssh</code> to stat1004 you can simply access the hive command-line interface by entering <kbd>hive</kbd>. Here's the start of a sample interactive  session:
<source lang="bash">
nuria@stat1004:~$ beeline
 
0: jdbc:hive2://analytics1003.eqiad.wmnet:100> show databases;
wmf
...
0: jdbc:hive2://analytics1003.eqiad.wmnet:100> use wmf;
No rows affected (0.019 seconds)
 
0: jdbc:hive2://analytics1003.eqiad.wmnet:100> show tables;
tab_name
aqs_hourly
browser_general
last_access_uniques_daily
last_access_uniques_monthly
mediacounts
mobile_apps_session_metrics
mobile_apps_session_metrics_by_os
mobile_apps_uniques_daily
mobile_apps_uniques_monthly
pagecounts_all_sites
pageview_hourly
pageview_unexpected_values
pageview_whitelist
projectview_hourly
webrequest
webrequest_empty
16 rows selected (0.028 seconds)
</source>
 
You can also write your query in a text file, e.g. named <tt>my_query.hql</tt>, and execute it. This example redirects the output of the query (by default in tab-separated values format) to <tt>results.txt</tt>:
<source lang=bash>
$ beeline -f my_query.hql > results.txt
</source>
 
 
=== Use wmf ===
 
You should <source lang=sql inline>use wmf</source> database (instead of the <code>wmf_raw</code> database) if you can, or your queries will be slow. The <code>wmf</code> database includes filtered and preprocessed data.
 
=== Output to a file ===
beeline -f my_query.hql > out.txt
 
=== Run queries in Cron ===
<syntaxhighlight lang="bash">
# The USER variable needs to be set with the username
# that will run the queries. Example: USER=elukey
USER=<user> /usr/local/bin/beeline -f my_query.hql > out.txt
</syntaxhighlight>
 
=== Always restrict queries to a date range (partitioning) ===
There is a ''lot'' of data in Hive, so you want to make sure your queries have time bounds so they complete in a reasonable amount of time.
For most tables you will be copying and pasting
: <kbd>WHERE year={{CURRENTYEAR}} AND month={{CURRENTMONTH1}} AND day={{CURRENTDAY}}</kbd>
to all your queries, and maybe appending <kbd>AND hour=''NN''</kbd> as well.
 
In fact, you ''must'' restrict your query to some Hive "partition", otherwise Hive will fail with the error "No partition predicate found". The partitioning of most tables is by year/month/day/hour.
For example, seeing this simple query fail:
<pre>
0: jdbc:hive2://analytics1003.eqiad.wmnet:100> SELECT agent_type FROM webrequest LIMIT 5;
Error: Error while compiling statement: FAILED: SemanticException [Error 10041]: No partition predicate found for Alias "webrequest" Table "webrequest" (state=42000,code=10041)</pre>
you must restrict it, for example:
<pre>
0: jdbc:hive2://analytics1003.eqiad.wmnet:100> SELECT agent_type FROM webrequest WHERE webrequest_source='misc' AND year=2016 AND month=4 AND day=29 and hour=0 LIMIT 5;
agent_type
user
user
user
spider
user
</pre>
 
=== Run long queries in a screen session and in the nice queue ===
* '''Screen''': If your queries take a long time to run you can execute them in a <code>[[w:GNU_Screen|screen]]</code> session. If you are accidentally disconnected, or you intentionally disconnect, from a screen session, your remote shell and queries continue to run.
* '''Nice queue''': For long queries, it's good practice to let fast queries take precedence for resources on the cluster. In order to do that, long queries should be run in the <code>nice</code> yarn queue. You can do so by
** In hive CLI, execute the command: <code>SET mapred.job.queue.name=nice;</code>
** In batch mode, add a parameter: <code>hive --hiveconf mapred.job.queue.name=nice ...</code>
 
=== User-defined functions ===
You can create or reuse UDFs (user-defined functions), see [[Analytics/Cluster/Hive/QueryUsingUDF]].
WMF has developed several UDFs, such as <code>is_wikimedia_bot</code>, <code>geocoded_data</code>, etc.
Some are run to create its derived tables from raw Hadoop information.
 
==Sample queries==
Here are sample queries from the tables in the <code>wmf</code> database. Run beeline and enter <source lang=sql inline>use wmf;</source> before running these.
 
=== Pageviews ===
Using the pre-aggregated table [[Analytics/Data/Pageview hourly|pageview_hourly]]
<source lang="SQL" enclose="div">
SELECT
  concat(month,'/',day,'/',year), access_method, sum(view_count)
FROM
  wmf.pageview_hourly
WHERE
  year = 2015
  AND month = 8
  AND agent_type = "user"
  AND country = "Canada"
  AND project = "en.wikipedia"
GROUP BY
  year, month, day, access_method;
</source>
===Distinct IPs===
<source lang="SQL" enclose="div">
SELECT
  COUNT(DISTINCT ip) AS hits
FROM
  webrequest
WHERE
  year = 2014
  AND month = 5
  AND day = 5
  AND hour = 13
  AND uri_host = "en.wikipedia.org"
  AND http_status = 200;
</source>
 
===Top raw referrals for an article===
<source lang="SQL" enclose="div">
SELECT
  referer,
  COUNT(DISTINCT ip) AS hits
FROM
  webrequest
WHERE
  year = 2014
  AND month = 5
  AND day = 5
  AND hour = 13
  AND uri_path = "/wiki/London"
  AND uri_host = "en.wikipedia.org"
  AND http_status = 200
GROUP BY referer
ORDER BY hits DESC
LIMIT 50;
</source>
 
===Top internal referrals for an article ===
<source lang="SQL" enclose="div">
SELECT
  SUBSTR(referer,30) AS source,
  COUNT(DISTINCT ip) AS hits
FROM
  webrequest
WHERE
  year = 2014
  AND month = 5
  AND day = 5
  AND hour = 13
  AND uri_path = "/wiki/London"
  AND uri_host = "en.wikipedia.org"
  AND referer LIKE "http://en.wikipedia.org/wiki/%"
  AND http_status = 200
GROUP BY
  SUBSTR(referer,30)
ORDER BY hits DESC
LIMIT 50;
</source>
 
===Top outbound clicks from an article ===
<source lang="SQL" enclose="div">
SELECT
  SUBSTR(uri_path,7) AS target,
  COUNT(DISTINCT ip) AS hits
FROM
  webrequest
WHERE
  year = 2014
  AND month = 5
  AND day = 5
  AND hour = 13
  AND uri_host = "en.wikipedia.org"
  AND referer LIKE "http://en.wikipedia.org/wiki/London%"
AND
  http_status = 200
GROUP BY SUBSTR(uri_path,7)
ORDER BY hits
DESC LIMIT 50;
</source>
 
==FAQ==
 
=== Counting rows which match a criterion ===
A common use case in querying data is counting the number of rows which match a criterion. With Hive, a good way to do this is by casting an expression which produces a boolean value to an integer and taking the sum. For example:<syntaxhighlight lang="sql">
select sum(cast(distinct_editors >= 500 as int))
from geowiki_monthly
where month = "2018-03"
</syntaxhighlight>This counts the number of rows where the <code>month</code> is March 2018 and the value of <code>distinct_editors</code> is greater than 500.
 
You can't simply use <code>count()</code> on a boolean expression, as in <code>count(distinct_editors >= 500)</code>, because that counts the rows where the expression is not null—which includes rows where it is false!
 
=== JsonSerDe Errors===
If you get errors like:
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.ClassNotFoundException Class org.apache.hive.hcatalog.data.JsonSerDe not found
 
then enter the following:
ADD JAR /usr/lib/hive-hcatalog/share/hcatalog/hive-hcatalog-core.jar ;
 
=== Killing a running query ===
Once you submit a query, it is handed off to Hadoop.  Hadoop runs the query as a YARN application.  The Hive CLI is then detached from the actual application.  If you Ctrl-C your Hive CLI, you will quit the interface you used to submit the query, but will not actually kill the application.  To kill the application, you have to tell YARN you want it dead.
 
Note the application ID from when your query started.  You should see something like:
 
  Starting Job = job_1387838787660_12241, Tracking URL = http://analytics1010.eqiad.wmnet:8088/proxy/application_1387838787660_12241/
 
The application ID in this case is  application_1387838787660_12241.  To kill this application, run:
 
  yarn application -kill application_1387838787660_12241
 
===Slow queries===
A simple SELECT LIMIT like the following will prepare all data from a partition before applying the LIMIT clause, so it's always best to specify the lowest-level relevant partition for the query.
<source lang="SQL" enclose="div">
SELECT http_status,uri_path FROM webrequest WHERE year = 2014 LIMIT 10;
</source>
===Scratch space===
You can create your own database using regular SQL
<source lang="SQL" enclose="div">
CREATE DATABASE dartar;
</source>
===Batch mode===
You can save the output of a hive query into a TSV by using hive in batch mode, e.g.
<source lang="bash" enclose="div">
hive  -e "USE wmf; SELECT foo;" > ./bar.tsv
</source>
===Dates===
Dates are expressed as integers due to how we have configured this data. You may encounter this error if you treat dates as strings by quoting:
<source lang="java" enclose="div">
> Diagnostic Messages for this Task:
> Error: java.io.IOException: java.lang.reflect.InvocationTargetException
>      [...]
> Caused by: java.lang.reflect.InvocationTargetException
>      [...]
> Caused by: java.io.EOFException
>      [...]
</source>
===Out of Memory Errors on Client===
When performing large queries, the hive client may run out of memory. Just look for an out of memory error at the top of the stack trace.
 
Invoke Hive via the following to allocate more memory
<source lang="bash" enclose="div">
export HADOOP_HEAPSIZE=2048 && beeline
</source>
 
===Out of Memory Errors when exporting large datasets===
If your query generates a large dataset and you are writing it out to a file (e.g. <code>beeline -f query.hql > output.tsv</code>), your query might fail due to beeline buffering all the data before writing it out, thereby running out of memory. Passing the parameter <code>--incremental=true</code> to make beeline print to stdout incrementally as data comes in. The previous example turns into the following command:
 
<source lang="bash" enclose="div">
beeline --incremental=true -f query.hql > output.tsv
</source>
 
Another solution is of course to switch to a data processing approach that does not require the data to be exported.
 
===Killing Jobs===
Standard Hadoop practice involves launching a long running job and then realizing you forgot a parameter. Here is how you kill a job from the command line:
 
<source lang="bash" enclose="div">
yarn application -kill [applicationID]
</source>
 
Your application id can be found in the hive messages -- it's the application_XXX string at the end of the tracking URL:
 
<source lang="bash" enclose="div">
Starting Job = job_1409078537822_2754, Tracking URL = http://analytics1001.eqiad.wmnet:8088/proxy/application_1409078537822_2754/
</source>
 
===INSERT OVERWRITE ... IF NOT EXISTS===
When using <code>INSERT OVERWRITE</code> with <code>IF NOT EXISTS</code> be sure to use unqualified tables. Otherwise, the <code>IF NOT EXISTS</code> gets silently ignored.
 
===Earliest data available in hadoop===
What’s the most efficient way to obtain the earliest, non-truncated hour or date of data still available in hadoop?
<source lang="bash" enclose="div"> 
show partitions webrequest;
</source>
Will show you all of the available partitions. Webrequest data in Hadoop is currently pruned after 62 days.
 
===Avoiding overgreedy scans / Operator precedence===
In HiveQL, <code>AND</code> takes precedence over <code>OR</code>.
 
So when trying to select all “mobile” and “text” partitions for 2015-01-01,
<source lang="sql"> 
  ... WHERE year=2015 AND month=1 AND day=1 AND webrequest_source='mobile' OR webrequest_source='text'
</source>
would not do the trick, as Hive parses it as
<source lang="sql"> 
  ... WHERE (year=2015 AND month=1 AND day=1 AND webrequest_source='mobile') OR webrequest_source='text'
</source>
and would select ''all'' “text” partitions. This unintentionally skews the data you want to obtain, makes the query take longer, and keeps all “text” partitions locked.
 
You can use parenthesis to override operator precedence. So for example
<source lang="sql"> 
  ... WHERE year=2015 AND month=1 AND day=1 AND (webrequest_source='mobile' OR webrequest_source='text')
</source>
would select the “mobile” and “text” partitions for 2015-01-01.
 
===My query does not start===
 
Check if the [[Analytics/Cluster/Hadoop/Load|cluster is overloaded]]. If it is, [[Analytics/Cluster/Hadoop/Load#What_to_do_if_the_cluster_is_stalling.3F|let the Analytics team know]].
 
===My query does not progress / finish===
 
Querying through Hive is not as fast as querying a plain SQL table. So expect to wait an hour for your result to show up.
 
But if the progress counter in your query does not increase (like at least a 1% per minute), you are either unintentionally querying a lot of data, or the cluster is stalled.
 
If you are querying “a lot” of data (a week worth of webrequest logs is definitely already more “a lot”!), consider splitting up your query. That will increase performance for you, cut down runtime of your query, and it will leave cluster resources available to other users. If you run into blockers, let the Analytics team know.
 
If your query is small, nice and neat, and does not progress nonetheless, please check if the [[Analytics/Cluster/Hadoop/Load|cluster is overloaded]]. If it is, [[Analytics/Cluster/Hadoop/Load#What_to_do_if_the_cluster_is_stalling.3F|let the Analytics team know]].
 
 
=== Search through logs ===
If your job is finished, you can find all of the job logs in HDFS at:
 
/mnt/hdfs/var/log/hadoop-yarn/apps/<user>/
 
Where ''USER'' is your username (<kbd>echo $USER</kbd> at a shell prompt) and ''application_id'' is the <code>application_''XXXXXX_XXXX''</code> number that yarn gives you when your app starts.
 
You can also access these via the <tt>yarn</tt> CLI, like:
 
  yarn logs -applicationId <applicationId>
 
=== More debgugging information in CLI ===
You can get more debugging information in your CLI by launching Hive with a special configuration option:
hive  -hiveconf hive.root.logger=INFO,console
 
=== Vertical query results ===
<code>!set outputformat vertical</code>

Latest revision as of 11:55, 12 January 2023

Redirect to:

Retrieved from "https://wikitech-static.wikimedia.org/w/index.php?title=Analytics/Systems/Cluster/Hive/Queries&oldid=596495"