http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/3be0f122/docs/topics/impala_show.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_show.xml b/docs/topics/impala_show.xml new file mode 100644 index 0000000..fbee88a --- /dev/null +++ b/docs/topics/impala_show.xml @@ -0,0 +1,1264 @@ +<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE concept PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> +<concept id="show"> + + <title>SHOW Statement</title> + <titlealts audience="PDF"><navtitle>SHOW</navtitle></titlealts> + <prolog> + <metadata> + <data name="Category" value="Impala"/> + <data name="Category" value="SQL"/> + <data name="Category" value="Developers"/> + <data name="Category" value="Data Analysts"/> + <data name="Category" value="Reports"/> + </metadata> + </prolog> + + <conbody> + + <p> + <indexterm audience="Cloudera">SHOW statement</indexterm> + The <codeph>SHOW</codeph> statement is a flexible way to get information about different types of Impala + objects. + </p> + + <p conref="../shared/impala_common.xml#common/syntax_blurb"/> + +<codeblock>SHOW DATABASES [[LIKE] '<varname>pattern</varname>'] +SHOW SCHEMAS [[LIKE] '<varname>pattern</varname>'] - an alias for SHOW DATABASES +SHOW TABLES [IN <varname>database_name</varname>] [[LIKE] '<varname>pattern</varname>'] +<ph rev="1.2.0">SHOW [AGGREGATE | ANALYTIC] FUNCTIONS [IN <varname>database_name</varname>] [[LIKE] '<varname>pattern</varname>']</ph> +<ph rev="1.2.1">SHOW CREATE TABLE [<varname>database_name</varname>].<varname>table_name</varname></ph> +<ph rev="1.2.1">SHOW TABLE STATS [<varname>database_name</varname>.]<varname>table_name</varname></ph> +<ph rev="1.2.1">SHOW COLUMN STATS [<varname>database_name</varname>.]<varname>table_name</varname></ph> +<ph rev="1.4.0">SHOW PARTITIONS [<varname>database_name</varname>.]<varname>table_name</varname></ph> +<ph rev="2.2.0">SHOW FILES IN [<varname>database_name</varname>.]<varname>table_name</varname> [PARTITION (<varname>key_col</varname>=<varname>value</varname> [, <varname>key_col</varname>=<varname>value</varname>]]</ph> + +<ph rev="2.0.0">SHOW ROLES +SHOW CURRENT ROLES +SHOW ROLE GRANT GROUP <varname>group_name</varname> +SHOW GRANT ROLE <varname>role_name</varname></ph> +</codeblock> + +<!-- SHOW ROLE GRANT { USER <varname>user_name</varname> | GROUP <varname>group_name</varname> | ROLE <varname>role_name</varname> } --> + +<!-- Extracted from the previous codeblock because even hidden content produces blank lines. +<ph audience="Cloudera" rev="1.4.0">SHOW DATA SOURCES [LIKE '<varname>source_name</varname>]</ph> +--> + +<!-- Some suggestion there would be this syntax for 1.4, but it's not in the builds: +<ph rev="1.4.0">SHOW [CACHED] TABLES [IN <varname>database_name</varname>] [[LIKE] '<varname>pattern</varname>']</ph> +<ph rev="1.4.0">SHOW [CACHED] PARTITIONS [<varname>database_name</varname>.]<varname>table_name</varname></ph> +--> + + <p> + Issue a <codeph>SHOW <varname>object_type</varname></codeph> statement to see the appropriate objects in the + current database, or <codeph>SHOW <varname>object_type</varname> IN <varname>database_name</varname></codeph> + to see objects in a specific database. + </p> + + <p> + The optional <varname>pattern</varname> argument is a quoted string literal, using Unix-style + <codeph>*</codeph> wildcards and allowing <codeph>|</codeph> for alternation. The preceding + <codeph>LIKE</codeph> keyword is also optional. All object names are stored in lowercase, so use all + lowercase letters in the pattern string. For example: + </p> + +<codeblock>show databases 'a*'; +show databases like 'a*'; +show tables in some_db like '*fact*'; +use some_db; +show tables '*dim*|*fact*';</codeblock> + + <p conref="../shared/impala_common.xml#common/cancel_blurb_no"/> + + <p outputclass="toc inpage"/> + + </conbody> + + <concept rev="2.2.0" id="show_files"> + + <title>SHOW FILES Statement</title> + <prolog> + <metadata> + <data name="Category" value="Disk Storage"/> + <data name="Category" value="Tables"/> + </metadata> + </prolog> + + <conbody> + + <p> + The <codeph>SHOW FILES</codeph> statement displays the files that constitute a specified table, + or a partition within a partitioned table. This syntax is available in <keyword keyref="impala22_full"/> and higher + only. The output includes the names of the files, the size of each file, and the applicable partition + for a partitioned table. The size includes a suffix of <codeph>B</codeph> for bytes, + <codeph>MB</codeph> for megabytes, and <codeph>GB</codeph> for gigabytes. + </p> + + <note> + This statement applies to tables and partitions stored on HDFS, or in the Amazon Simple Storage System (S3). + It does not apply to views. + It does not apply to tables mapped onto HBase, because HBase does not use the same file-based storage layout. + </note> + + <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> + + <p> + You can use this statement to verify the results of your ETL process: that is, that + the expected files are present, with the expected sizes. You can examine the file information + to detect conditions such as empty files, missing files, or inefficient layouts due to + a large number of small files. When you use <codeph>INSERT</codeph> statements to copy + from one table to another, you can see how the file layout changes due to file format + conversions, compaction of small input files into large data blocks, and + multiple output files from parallel queries and partitioned inserts. + </p> + + <p> + The output from this statement does not include files that Impala considers to be hidden + or invisible, such as those whose names start with a dot or an underscore, or that + end with the suffixes <codeph>.copying</codeph> or <codeph>.tmp</codeph>. + </p> + + <p> + The information for partitioned tables complements the output of the <codeph>SHOW PARTITIONS</codeph> + statement, which summarizes information about each partition. <codeph>SHOW PARTITIONS</codeph> + produces some output for each partition, while <codeph>SHOW FILES</codeph> does not + produce any output for empty partitions because they do not include any data files. + </p> + +<!-- Extensive round of testing makes me pretty confident of these findings. --> + <p conref="../shared/impala_common.xml#common/permissions_blurb"/> + <p rev="CDH-19187"> + The user ID that the <cmdname>impalad</cmdname> daemon runs under, + typically the <codeph>impala</codeph> user, must have read + permission for all the table files, read and execute permission for all the directories that make up the table, + and execute permission for the database directory and all its parent directories. + </p> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + + <p> + The following example shows a <codeph>SHOW FILES</codeph> statement + for an unpartitioned table using text format: + </p> + +<codeblock scale="60">[localhost:21000] > create table unpart_text (x bigint, s string); +[localhost:21000] > insert into unpart_text (x, s) select id, name + > from oreilly.sample_data limit 20e6; +[localhost:21000] > show files in unpart_text; ++------------------------------------------------------------------------------+----------+-----------+ +| path | size | partition | ++------------------------------------------------------------------------------+----------+-----------+ +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_text/35665776ef85cfaf_1012432410_data.0. | 448.31MB | | ++------------------------------------------------------------------------------+----------+-----------+ +[localhost:21000] > insert into unpart_text (x, s) select id, name from oreilly.sample_data limit 100e6; +[localhost:21000] > show files in unpart_text; ++--------------------------------------------------------------------------------------+----------+-----------+ +| path | size | partition | ++--------------------------------------------------------------------------------------+----------+-----------+ +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_text/35665776ef85cfaf_1012432410_data.0. | 448.31MB | | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_text/ac3dba252a8952b8_1663177415_data.0. | 2.19GB | | ++--------------------------------------------------------------------------------------+----------+-----------+ +</codeblock> + + <p> + This example illustrates how, after issuing some <codeph>INSERT ... VALUES</codeph> statements, + the table now contains some tiny files of just a few bytes. Such small files could cause inefficient processing of + parallel queries that are expecting multi-megabyte input files. The example shows how you might compact the small files by doing + an <codeph>INSERT ... SELECT</codeph> into a different table, possibly converting the data to Parquet in the process: + </p> + +<codeblock scale="60">[localhost:21000] > insert into unpart_text values (10,'hello'), (20, 'world'); +[localhost:21000] > insert into unpart_text values (-1,'foo'), (-1000, 'bar'); +[localhost:21000] > show files in unpart_text; ++--------------------------------------------------------------------------------------+----------+ +| path | size | ++--------------------------------------------------------------------------------------+----------+ +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_text/4f11b8bdf8b6aa92_238145083_data.0. | 18B +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_text/35665776ef85cfaf_1012432410_data.0. | 448.31MB +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_text/ac3dba252a8952b8_1663177415_data.0. | 2.19GB +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_text/cfb8252452445682_1868457216_data.0. | 17B ++--------------------------------------------------------------------------------------+----------+ +[localhost:21000] > create table unpart_parq stored as parquet as select * from unpart_text; ++---------------------------+ +| summary | ++---------------------------+ +| Inserted 120000002 row(s) | ++---------------------------+ +[localhost:21000] > show files in unpart_parq; ++------------------------------------------------------------------------------------------+----------+ +| path | size | ++------------------------------------------------------------------------------------------+----------+ +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_parq/60798d96ba630184_549959007_data.0.parq | 255.36MB | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_parq/60798d96ba630184_549959007_data.1.parq | 178.52MB | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_parq/60798d96ba630185_549959007_data.0.parq | 255.37MB | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_parq/60798d96ba630185_549959007_data.1.parq | 57.71MB | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_parq/60798d96ba630186_2141167244_data.0.parq | 255.40MB | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_parq/60798d96ba630186_2141167244_data.1.parq | 175.52MB | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_parq/60798d96ba630187_1006832086_data.0.parq | 255.40MB | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/unpart_parq/60798d96ba630187_1006832086_data.1.parq | 214.61MB | ++------------------------------------------------------------------------------------------+----------+ +</codeblock> + + <p> + The following example shows a <codeph>SHOW FILES</codeph> statement for a partitioned text table + with data in two different partitions, and two empty partitions. + The partitions with no data are not represented in the <codeph>SHOW FILES</codeph> output. + </p> +<codeblock scale="60">[localhost:21000] > create table part_text (x bigint, y int, s string) + > partitioned by (year bigint, month bigint, day bigint); +[localhost:21000] > insert overwrite part_text (x, y, s) partition (year=2014,month=1,day=1) + > select id, val, name from oreilly.normalized_parquet +where id between 1 and 1000000; +[localhost:21000] > insert overwrite part_text (x, y, s) partition (year=2014,month=1,day=2) + > select id, val, name from oreilly.normalized_parquet + > where id between 1000001 and 2000000; +[localhost:21000] > alter table part_text add partition (year=2014,month=1,day=3); +[localhost:21000] > alter table part_text add partition (year=2014,month=1,day=4); +[localhost:21000] > show partitions part_text; ++-------+-------+-----+-------+--------+---------+--------------+-------------------+--------+-------------------+ +| year | month | day | #Rows | #Files | Size | Bytes Cached | Cache Replication | Format | Incremental stats | ++-------+-------+-----+-------+--------+---------+--------------+-------------------+--------+-------------------+ +| 2014 | 1 | 1 | -1 | 4 | 25.16MB | NOT CACHED | NOT CACHED | TEXT | false | +| 2014 | 1 | 2 | -1 | 4 | 26.22MB | NOT CACHED | NOT CACHED | TEXT | false | +| 2014 | 1 | 3 | -1 | 0 | 0B | NOT CACHED | NOT CACHED | TEXT | false | +| 2014 | 1 | 4 | -1 | 0 | 0B | NOT CACHED | NOT CACHED | TEXT | false | +| Total | | | -1 | 8 | 51.38MB | 0B | | | | ++-------+-------+-----+-------+--------+---------+--------------+-------------------+--------+-------------------+ +[localhost:21000] > show files in part_text; ++---------------------------------------------------------------------------------------------------------+--------+-------------------------+ +| path | size | partition | ++---------------------------------------------------------------------------------------------------------+--------+-------------------------+ +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_text/year=2014/month=1/day=1/80732d9dc80689f_1418645991_data.0. | 5.77MB | year=2014/month=1/day=1 | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_text/year=2014/month=1/day=1/80732d9dc8068a0_1418645991_data.0. | 6.25MB | year=2014/month=1/day=1 | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_text/year=2014/month=1/day=1/80732d9dc8068a1_147082319_data.0. | 7.16MB | year=2014/month=1/day=1 | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_text/year=2014/month=1/day=1/80732d9dc8068a2_2111411753_data.0. | 5.98MB | year=2014/month=1/day=1 | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_text/year=2014/month=1/day=2/21a828cf494b5bbb_501271652_data.0. | 6.42MB | year=2014/month=1/day=2 | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_text/year=2014/month=1/day=2/21a828cf494b5bbc_501271652_data.0. | 6.62MB | year=2014/month=1/day=2 | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_text/year=2014/month=1/day=2/21a828cf494b5bbd_1393490200_data.0. | 6.98MB | year=2014/month=1/day=2 | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_text/year=2014/month=1/day=2/21a828cf494b5bbe_1393490200_data.0. | 6.20MB | year=2014/month=1/day=2 | ++---------------------------------------------------------------------------------------------------------+--------+-------------------------+ +</codeblock> + <p> + The following example shows a <codeph>SHOW FILES</codeph> statement for a partitioned Parquet table. + The number and sizes of files are different from the equivalent partitioned text table + used in the previous example, because <codeph>INSERT</codeph> operations for Parquet tables + are parallelized differently than for text tables. (Also, the amount of data is so small + that it can be written to Parquet without involving all the hosts in this 4-node cluster.) + </p> +<codeblock scale="60">[localhost:21000] > create table part_parq (x bigint, y int, s string) partitioned by (year bigint, month bigint, day bigint) stored as parquet; +[localhost:21000] > insert into part_parq partition (year,month,day) select x, y, s, year, month, day from partitioned_text; +[localhost:21000] > show partitions part_parq; ++-------+-------+-----+-------+--------+---------+--------------+-------------------+---------+-------------------+ +| year | month | day | #Rows | #Files | Size | Bytes Cached | Cache Replication | Format | Incremental stats | ++-------+-------+-----+-------+--------+---------+--------------+-------------------+---------+-------------------+ +| 2014 | 1 | 1 | -1 | 3 | 17.89MB | NOT CACHED | NOT CACHED | PARQUET | false | +| 2014 | 1 | 2 | -1 | 3 | 17.89MB | NOT CACHED | NOT CACHED | PARQUET | false | +| Total | | | -1 | 6 | 35.79MB | 0B | | | | ++-------+-------+-----+-------+--------+---------+--------------+-------------------+---------+-------------------+ +[localhost:21000] > show files in part_parq; ++-----------------------------------------------------------------------------------------------+--------+-------------------------+ +| path | size | partition | ++-----------------------------------------------------------------------------------------------+--------+-------------------------+ +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_parq/year=2014/month=1/day=1/1134113650_data.0.parq | 4.49MB | year=2014/month=1/day=1 | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_parq/year=2014/month=1/day=1/617567880_data.0.parq | 5.14MB | year=2014/month=1/day=1 | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_parq/year=2014/month=1/day=1/2099499416_data.0.parq | 8.27MB | year=2014/month=1/day=1 | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_parq/year=2014/month=1/day=2/945567189_data.0.parq | 8.80MB | year=2014/month=1/day=2 | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_parq/year=2014/month=1/day=2/2145850112_data.0.parq | 4.80MB | year=2014/month=1/day=2 | +| hdfs://<varname>impala_data_dir</varname>/show_files.db/part_parq/year=2014/month=1/day=2/665613448_data.0.parq | 4.29MB | year=2014/month=1/day=2 | ++-----------------------------------------------------------------------------------------------+--------+-------------------------+ +</codeblock> +<p> + The following example shows output from the <codeph>SHOW FILES</codeph> statement + for a table where the data files are stored in Amazon S3: +</p> +<codeblock scale="70">[localhost:21000] > show files in s3_testing.sample_data_s3; ++-----------------------------------------------------------------------+---------+ +| path | size | ++-----------------------------------------------------------------------+---------+ +| s3a://impala-demo/sample_data/e065453cba1988a6_1733868553_data.0.parq | 24.84MB | ++-----------------------------------------------------------------------+---------+ +</codeblock> +<!-- + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + <xref href="impala_authorization.xml#authorization"/> + </p> +--> + </conbody> + </concept> + + <concept rev="2.0.0" id="show_roles"> + + <title>SHOW ROLES Statement</title> + <prolog> + <metadata> + <data name="Category" value="Sentry"/> + <data name="Category" value="Roles"/> + </metadata> + </prolog> + + <conbody> + + <p> + The <codeph>SHOW ROLES</codeph> statement displays roles. This syntax is available in CDH 5.2 and later + only, when you are using the Sentry authorization framework along with the Sentry service, as described in + <xref href="impala_authorization.xml#sentry_service"/>. It does not apply when you use the Sentry framework + with privileges defined in a policy file. + </p> + + <p conref="../shared/impala_common.xml#common/security_blurb"/> + + <p conref="../shared/impala_common.xml#common/show_security"/> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + + <p> + Depending on the roles set up within your organization by the <codeph>CREATE ROLE</codeph> statement, the + output might look something like this: + </p> + +<codeblock>show roles; ++-----------+ +| role_name | ++-----------+ +| analyst | +| role1 | +| sales | +| superuser | +| test_role | ++-----------+ +</codeblock> + + <p conref="../shared/impala_common.xml#common/permissions_blurb_no"/> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + <xref href="impala_authorization.xml#authorization"/> + </p> + </conbody> + </concept> + + <concept rev="2.0.0" id="show_current_role"> + + <title>SHOW CURRENT ROLE</title> + <prolog> + <metadata> + <data name="Category" value="Sentry"/> + <data name="Category" value="Roles"/> + </metadata> + </prolog> + + <conbody> + + <p rev="2.0.0"> + The <codeph>SHOW CURRENT ROLE</codeph> statement displays roles assigned to the current user. This syntax + is available in CDH 5.2 and later only, when you are using the Sentry authorization framework along with + the Sentry service, as described in <xref href="impala_authorization.xml#sentry_service"/>. It does not + apply when you use the Sentry framework with privileges defined in a policy file. + </p> + + <p conref="../shared/impala_common.xml#common/security_blurb"/> + + <p conref="../shared/impala_common.xml#common/show_security"/> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + + <p> + Depending on the roles set up within your organization by the <codeph>CREATE ROLE</codeph> statement, the + output might look something like this: + </p> + +<codeblock>show current roles; ++-----------+ +| role_name | ++-----------+ +| role1 | +| superuser | ++-----------+ +</codeblock> + + <p conref="../shared/impala_common.xml#common/permissions_blurb_no"/> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + <xref href="impala_authorization.xml#authorization"/> + </p> + </conbody> + </concept> + + <concept id="show_role_grant"> + + <title>SHOW ROLE GRANT Statement</title> + <prolog> + <metadata> + <data name="Category" value="Sentry"/> + <data name="Category" value="Roles"/> + </metadata> + </prolog> + + + <conbody> + + <p rev="2.0.0"> +<!-- Copied from Sentry docs. Turn into conref. I did some rewording for clarity. --> + The <codeph>SHOW ROLE GRANT</codeph> statement lists all the roles assigned to the specified group. This + statement is only allowed for Sentry administrative users and others users that are part of the specified + group. This syntax is available in CDH 5.2 and later only, when you are using the Sentry authorization + framework along with the Sentry service, as described in + <xref href="impala_authorization.xml#sentry_service"/>. It does not apply when you use the Sentry framework + with privileges defined in a policy file. + </p> + + <p conref="../shared/impala_common.xml#common/security_blurb"/> + + <p conref="../shared/impala_common.xml#common/show_security"/> + +<!-- +<p conref="../shared/impala_common.xml#common/example_blurb"/> +<codeblock>To do: construct example for SHOW ROLE GRANT</codeblock> +--> + + <p conref="../shared/impala_common.xml#common/permissions_blurb_no"/> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + <xref href="impala_authorization.xml#authorization"/> + </p> + </conbody> + </concept> + + <concept rev="2.0.0" id="show_grant_role"> + + <title>SHOW GRANT ROLE Statement</title> + <prolog> + <metadata> + <data name="Category" value="Sentry"/> + <data name="Category" value="Roles"/> + </metadata> + </prolog> + + + <conbody> + + <p> +<!-- Copied from Sentry docs. Turn into conref. I did some rewording for clarity. --> + The <codeph>SHOW GRANT ROLE</codeph> statement list all the grants for the given role name. This statement + is only allowed for Sentry administrative users and other users that have been granted the specified role. + This syntax is available in CDH 5.2 and later only, when you are using the Sentry authorization framework + along with the Sentry service, as described in <xref href="impala_authorization.xml#sentry_service"/>. It + does not apply when you use the Sentry framework with privileges defined in a policy file. + </p> + + <p conref="../shared/impala_common.xml#common/security_blurb"/> + + <p conref="../shared/impala_common.xml#common/show_security"/> + +<!-- +<p conref="../shared/impala_common.xml#common/example_blurb"/> +<codeblock>To do: construct example for SHOW GRANT ROLE</codeblock> +--> + + <p conref="../shared/impala_common.xml#common/permissions_blurb_no"/> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + <xref href="impala_authorization.xml#authorization"/> + </p> + </conbody> + </concept> + + <concept id="show_databases"> + + <title>SHOW DATABASES</title> + <prolog> + <metadata> + <data name="Category" value="Databases"/> + <data name="Category" value="Schemas"/> + </metadata> + </prolog> + + <conbody> + + <p> + The <codeph>SHOW DATABASES</codeph> statement is often the first one you issue when connecting to an + instance for the first time. You typically issue <codeph>SHOW DATABASES</codeph> to see the names you can + specify in a <codeph>USE <varname>db_name</varname></codeph> statement, then after switching to a database + you issue <codeph>SHOW TABLES</codeph> to see the names you can specify in <codeph>SELECT</codeph> and + <codeph>INSERT</codeph> statements. + </p> + + <p rev="2.5.0 IMPALA-2070"> + In <keyword keyref="impala25_full"/> and higher, the output includes a second column showing any associated comment + for each database. + </p> + + <p> + The output of <codeph>SHOW DATABASES</codeph> includes the special <codeph>_impala_builtins</codeph> + database, which lets you view definitions of built-in functions, as described under <codeph>SHOW + FUNCTIONS</codeph>. + </p> + + <p conref="../shared/impala_common.xml#common/security_blurb"/> + + <p conref="../shared/impala_common.xml#common/show_security"/> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + + <p> + This example shows how you might locate a particular table on an unfamiliar system. The + <codeph>DEFAULT</codeph> database is the one you initially connect to; a database with that name is present + on every system. You can issue <codeph>SHOW TABLES IN <varname>db_name</varname></codeph> without going + into a database, or <codeph>SHOW TABLES</codeph> once you are inside a particular database. + </p> + +<codeblock rev="2.5.0 IMPALA-2070">[localhost:21000] > show databases; ++------------------+----------------------------------------------+ +| name | comment | ++------------------+----------------------------------------------+ +| _impala_builtins | System database for Impala builtin functions | +| default | Default Hive database | +| file_formats | | ++------------------+----------------------------------------------+ +Returned 3 row(s) in 0.02s +[localhost:21000] > show tables in file_formats; ++--------------------+ +| name | ++--------------------+ +| parquet_table | +| rcfile_table | +| sequencefile_table | +| textfile_table | ++--------------------+ +Returned 4 row(s) in 0.01s +[localhost:21000] > use file_formats; +[localhost:21000] > show tables like '*parq*'; ++--------------------+ +| name | ++--------------------+ +| parquet_table | ++--------------------+ +Returned 1 row(s) in 0.01s</codeblock> + + <p conref="../shared/impala_common.xml#common/permissions_blurb_no"/> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + <xref href="impala_databases.xml#databases"/>, <xref href="impala_create_database.xml#create_database"/>, + <xref href="impala_drop_database.xml#drop_database"/>, <xref href="impala_use.xml#use"/> + <xref href="impala_show.xml#show_tables"/>, + <xref href="impala_show.xml#show_functions"/> + </p> + </conbody> + </concept> + + <concept id="show_tables"> + + <title>SHOW TABLES Statement</title> + <prolog> + <metadata> + <data name="Category" value="Tables"/> + <data name="Category" value="Schemas"/> + </metadata> + </prolog> + + <conbody> + + <p> + Displays the names of tables. By default, lists tables in the current database, or with the + <codeph>IN</codeph> clause, in a specified database. By default, lists all tables, or with the + <codeph>LIKE</codeph> clause, only those whose name match a pattern with <codeph>*</codeph> wildcards. + </p> + + <p conref="../shared/impala_common.xml#common/security_blurb"/> + + <p conref="../shared/impala_common.xml#common/show_security"/> + + <p rev="CDH-19187"> + The user ID that the <cmdname>impalad</cmdname> daemon runs under, + typically the <codeph>impala</codeph> user, must have read and execute + permissions for all directories that are part of the table. + (A table could span multiple different HDFS directories if it is partitioned. + The directories could be widely scattered because a partition can reside + in an arbitrary HDFS directory based on its <codeph>LOCATION</codeph> attribute.) + </p> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + + <p> + The following examples demonstrate the <codeph>SHOW TABLES</codeph> statement. + If the database contains no tables, the result set is empty. + If the database does contain tables, <codeph>SHOW TABLES IN <varname>db_name</varname></codeph> + lists all the table names. <codeph>SHOW TABLES</codeph> with no qualifiers lists + all the table names in the current database. + </p> + +<codeblock>create database empty_db; +show tables in empty_db; +Fetched 0 row(s) in 0.11s + +create database full_db; +create table full_db.t1 (x int); +create table full_db.t2 like full_db.t1; + +show tables in full_db; ++------+ +| name | ++------+ +| t1 | +| t2 | ++------+ + +use full_db; +show tables; ++------+ +| name | ++------+ +| t1 | +| t2 | ++------+ +</codeblock> + + <p> + This example demonstrates how <codeph>SHOW TABLES LIKE '<varname>wildcard_pattern</varname>'</codeph> + lists table names that match a pattern, or multiple alternative patterns. + The ability to do wildcard matches for table names makes it helpful to establish naming conventions for tables to + conveniently locate a group of related tables. + </p> + +<codeblock>create table fact_tbl (x int); +create table dim_tbl_1 (s string); +create table dim_tbl_2 (s string); + +/* Asterisk is the wildcard character. Only 2 out of the 3 just-created tables are returned. */ +show tables like 'dim*'; ++-----------+ +| name | ++-----------+ +| dim_tbl_1 | +| dim_tbl_2 | ++-----------+ + +/* We are already in the FULL_DB database, but just to be sure we can specify the database name also. */ +show tables in full_db like 'dim*'; ++-----------+ +| name | ++-----------+ +| dim_tbl_1 | +| dim_tbl_2 | ++-----------+ + +/* The pipe character separates multiple wildcard patterns. */ +show tables like '*dim*|t*'; ++-----------+ +| name | ++-----------+ +| dim_tbl_1 | +| dim_tbl_2 | +| t1 | +| t2 | ++-----------+ +</codeblock> + + <p conref="../shared/impala_common.xml#common/permissions_blurb_no"/> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + <xref href="impala_tables.xml#tables"/>, <xref href="impala_create_table.xml#create_table"/>, + <xref href="impala_alter_table.xml#alter_table"/>, <xref href="impala_drop_table.xml#drop_table"/>, + <xref href="impala_describe.xml#describe"/>, <xref href="impala_show.xml#show_create_table"/>, + <xref href="impala_show.xml#show_table_stats"/>, + <xref href="impala_show.xml#show_databases"/>, + <xref href="impala_show.xml#show_functions"/> + </p> + </conbody> + </concept> + + <concept rev="1.2.1" id="show_create_table"> + + <title>SHOW CREATE TABLE Statement</title> + <prolog> + <metadata> + <data name="Category" value="Schemas"/> + <data name="Category" value="Impala Data Types"/> + </metadata> + </prolog> + + <conbody> + + <p> + As a schema changes over time, you might run a <codeph>CREATE TABLE</codeph> statement followed by several + <codeph>ALTER TABLE</codeph> statements. To capture the cumulative effect of all those statements, + <codeph>SHOW CREATE TABLE</codeph> displays a <codeph>CREATE TABLE</codeph> statement that would reproduce + the current structure of a table. You can use this output in scripts that set up or clone a group of + tables, rather than trying to reproduce the original sequence of <codeph>CREATE TABLE</codeph> and + <codeph>ALTER TABLE</codeph> statements. When creating variations on the original table, or cloning the + original table on a different system, you might need to edit the <codeph>SHOW CREATE TABLE</codeph> output + to change things such as the database name, <codeph>LOCATION</codeph> field, and so on that might be + different on the destination system. + </p> + + <p conref="../shared/impala_common.xml#common/security_blurb"/> + + <p conref="../shared/impala_common.xml#common/show_security"/> + + <p conref="../shared/impala_common.xml#common/permissions_blurb_no"/> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + + <p> + The following example shows how various clauses from the <codeph>CREATE TABLE</codeph> statement are + represented in the output of <codeph>SHOW CREATE TABLE</codeph>. + </p> + +<codeblock>create table show_create_table_demo (id int comment "Unique ID", y double, s string) + partitioned by (year smallint) + stored as parquet; + +show create table show_create_table_demo; ++----------------------------------------------------------------------------------------+ +| result | ++----------------------------------------------------------------------------------------+ +| CREATE TABLE scratch.show_create_table_demo ( | +| id INT COMMENT 'Unique ID', | +| y DOUBLE, | +| s STRING | +| ) | +| PARTITIONED BY ( | +| year SMALLINT | +| ) | +| STORED AS PARQUET | +| LOCATION 'hdfs://127.0.0.1:8020/user/hive/warehouse/scratch.db/show_create_table_demo' | +| TBLPROPERTIES ('transient_lastDdlTime'='1418152582') | ++----------------------------------------------------------------------------------------+ +</codeblock> + + <p> + The following example shows how, after a sequence of <codeph>ALTER TABLE</codeph> statements, the output + from <codeph>SHOW CREATE TABLE</codeph> represents the current state of the table. This output could be + used to create a matching table rather than executing the original <codeph>CREATE TABLE</codeph> and + sequence of <codeph>ALTER TABLE</codeph> statements. + </p> + +<codeblock>alter table show_create_table_demo drop column s; +alter table show_create_table_demo set fileformat textfile; + +show create table show_create_table_demo; ++----------------------------------------------------------------------------------------+ +| result | ++----------------------------------------------------------------------------------------+ +| CREATE TABLE scratch.show_create_table_demo ( | +| id INT COMMENT 'Unique ID', | +| y DOUBLE | +| ) | +| PARTITIONED BY ( | +| year SMALLINT | +| ) | +| STORED AS TEXTFILE | +| LOCATION 'hdfs://127.0.0.1:8020/user/hive/warehouse/demo.db/show_create_table_demo' | +| TBLPROPERTIES ('transient_lastDdlTime'='1418152638') | ++----------------------------------------------------------------------------------------+ +</codeblock> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + <xref href="impala_create_table.xml#create_table"/>, <xref href="impala_describe.xml#describe"/>, + <xref href="impala_show.xml#show_tables"/> + </p> + </conbody> + </concept> + + <concept id="show_table_stats"> + + <title>SHOW TABLE STATS Statement</title> + <prolog> + <metadata> + <data name="Category" value="Performance"/> + </metadata> + </prolog> + + <conbody> + + <p> + The <codeph>SHOW TABLE STATS</codeph> and <codeph>SHOW COLUMN STATS</codeph> variants are important for + tuning performance and diagnosing performance issues, especially with the largest tables and the most + complex join queries. + </p> + + <p> + Any values that are not available (because the <codeph>COMPUTE STATS</codeph> statement has not been run + yet) are displayed as <codeph>-1</codeph>. + </p> + + <p> + <codeph>SHOW TABLE STATS</codeph> provides some general information about the table, such as the number of + files, overall size of the data, whether some or all of the data is in the HDFS cache, and the file format, + that is useful whether or not you have run the <codeph>COMPUTE STATS</codeph> statement. A + <codeph>-1</codeph> in the <codeph>#Rows</codeph> output column indicates that the <codeph>COMPUTE + STATS</codeph> statement has never been run for this table. If the table is partitioned, <codeph>SHOW TABLE + STATS</codeph> provides this information for each partition. (It produces the same output as the + <codeph>SHOW PARTITIONS</codeph> statement in this case.) + </p> + + <p> + The output of <codeph>SHOW COLUMN STATS</codeph> is primarily only useful after the <codeph>COMPUTE + STATS</codeph> statement has been run on the table. A <codeph>-1</codeph> in the <codeph>#Distinct + Values</codeph> output column indicates that the <codeph>COMPUTE STATS</codeph> statement has never been + run for this table. Currently, Impala always leaves the <codeph>#Nulls</codeph> column as + <codeph>-1</codeph>, even after <codeph>COMPUTE STATS</codeph> has been run. + </p> + + <p> + These <codeph>SHOW</codeph> statements work on actual tables only, not on views. + </p> + + <p conref="../shared/impala_common.xml#common/security_blurb"/> + + <p conref="../shared/impala_common.xml#common/show_security"/> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + + <p> + The following examples show how the <codeph>SHOW TABLE STATS</codeph> statement displays physical + information about a table and the associated data files: + </p> + +<codeblock>show table stats store_sales; ++-------+--------+----------+--------------+--------+-------------------+ +| #Rows | #Files | Size | Bytes Cached | Format | Incremental stats | ++-------+--------+----------+--------------+--------+-------------------+ +| -1 | 1 | 370.45MB | NOT CACHED | TEXT | false | ++-------+--------+----------+--------------+--------+-------------------+ + +show table stats customer; ++-------+--------+---------+--------------+--------+-------------------+ +| #Rows | #Files | Size | Bytes Cached | Format | Incremental stats | ++-------+--------+---------+--------------+--------+-------------------+ +| -1 | 1 | 12.60MB | NOT CACHED | TEXT | false | ++-------+--------+---------+--------------+--------+-------------------+ +</codeblock> + + <p> + The following example shows how, after a <codeph>COMPUTE STATS</codeph> or <codeph>COMPUTE INCREMENTAL + STATS</codeph> statement, the <codeph>#Rows</codeph> field is now filled in. Because the + <codeph>STORE_SALES</codeph> table in this example is not partitioned, the <codeph>COMPUTE INCREMENTAL + STATS</codeph> statement produces regular stats rather than incremental stats, therefore the + <codeph>Incremental stats</codeph> field remains <codeph>false</codeph>. + </p> + +<codeblock>compute stats customer; ++------------------------------------------+ +| summary | ++------------------------------------------+ +| Updated 1 partition(s) and 18 column(s). | ++------------------------------------------+ + +show table stats customer; ++--------+--------+---------+--------------+--------+-------------------+ +| #Rows | #Files | Size | Bytes Cached | Format | Incremental stats | ++--------+--------+---------+--------------+--------+-------------------+ +| 100000 | 1 | 12.60MB | NOT CACHED | TEXT | false | ++--------+--------+---------+--------------+--------+-------------------+ + +compute incremental stats store_sales; ++------------------------------------------+ +| summary | ++------------------------------------------+ +| Updated 1 partition(s) and 23 column(s). | ++------------------------------------------+ + +show table stats store_sales; ++---------+--------+----------+--------------+--------+-------------------+ +| #Rows | #Files | Size | Bytes Cached | Format | Incremental stats | ++---------+--------+----------+--------------+--------+-------------------+ +| 2880404 | 1 | 370.45MB | NOT CACHED | TEXT | false | ++---------+--------+----------+--------------+--------+-------------------+ +</codeblock> + + <p conref="../shared/impala_common.xml#common/permissions_blurb"/> + <p rev="CDH-19187"> + The user ID that the <cmdname>impalad</cmdname> daemon runs under, + typically the <codeph>impala</codeph> user, must have read and execute + permissions for all directories that are part of the table. + (A table could span multiple different HDFS directories if it is partitioned. + The directories could be widely scattered because a partition can reside + in an arbitrary HDFS directory based on its <codeph>LOCATION</codeph> attribute.) + The Impala user must also have execute + permission for the database directory, and any parent directories of the database directory in HDFS. + </p> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + <xref href="impala_compute_stats.xml#compute_stats"/>, <xref href="impala_show.xml#show_column_stats"/> + </p> + + <p> + See <xref href="impala_perf_stats.xml#perf_stats"/> for usage information and examples. + </p> + </conbody> + </concept> + + <concept id="show_column_stats"> + + <title>SHOW COLUMN STATS Statement</title> + <prolog> + <metadata> + <data name="Category" value="Performance"/> + </metadata> + </prolog> + + <conbody> + + <p> + The <codeph>SHOW TABLE STATS</codeph> and <codeph>SHOW COLUMN STATS</codeph> variants are important for + tuning performance and diagnosing performance issues, especially with the largest tables and the most + complex join queries. + </p> + + <p conref="../shared/impala_common.xml#common/security_blurb"/> + + <p conref="../shared/impala_common.xml#common/show_security"/> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + + <p> + The following examples show the output of the <codeph>SHOW COLUMN STATS</codeph> statement for some tables, + before the <codeph>COMPUTE STATS</codeph> statement is run. Impala deduces some information, such as + maximum and average size for fixed-length columns, and leaves and unknown values as <codeph>-1</codeph>. + </p> + +<codeblock>show column stats customer; ++------------------------+--------+------------------+--------+----------+----------+ +| Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size | ++------------------------+--------+------------------+--------+----------+----------+ +| c_customer_sk | INT | -1 | -1 | 4 | 4 | +| c_customer_id | STRING | -1 | -1 | -1 | -1 | +| c_current_cdemo_sk | INT | -1 | -1 | 4 | 4 | +| c_current_hdemo_sk | INT | -1 | -1 | 4 | 4 | +| c_current_addr_sk | INT | -1 | -1 | 4 | 4 | +| c_first_shipto_date_sk | INT | -1 | -1 | 4 | 4 | +| c_first_sales_date_sk | INT | -1 | -1 | 4 | 4 | +| c_salutation | STRING | -1 | -1 | -1 | -1 | +| c_first_name | STRING | -1 | -1 | -1 | -1 | +| c_last_name | STRING | -1 | -1 | -1 | -1 | +| c_preferred_cust_flag | STRING | -1 | -1 | -1 | -1 | +| c_birth_day | INT | -1 | -1 | 4 | 4 | +| c_birth_month | INT | -1 | -1 | 4 | 4 | +| c_birth_year | INT | -1 | -1 | 4 | 4 | +| c_birth_country | STRING | -1 | -1 | -1 | -1 | +| c_login | STRING | -1 | -1 | -1 | -1 | +| c_email_address | STRING | -1 | -1 | -1 | -1 | +| c_last_review_date | STRING | -1 | -1 | -1 | -1 | ++------------------------+--------+------------------+--------+----------+----------+ + +show column stats store_sales; ++-----------------------+-------+------------------+--------+----------+----------+ +| Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size | ++-----------------------+-------+------------------+--------+----------+----------+ +| ss_sold_date_sk | INT | -1 | -1 | 4 | 4 | +| ss_sold_time_sk | INT | -1 | -1 | 4 | 4 | +| ss_item_sk | INT | -1 | -1 | 4 | 4 | +| ss_customer_sk | INT | -1 | -1 | 4 | 4 | +| ss_cdemo_sk | INT | -1 | -1 | 4 | 4 | +| ss_hdemo_sk | INT | -1 | -1 | 4 | 4 | +| ss_addr_sk | INT | -1 | -1 | 4 | 4 | +| ss_store_sk | INT | -1 | -1 | 4 | 4 | +| ss_promo_sk | INT | -1 | -1 | 4 | 4 | +| ss_ticket_number | INT | -1 | -1 | 4 | 4 | +| ss_quantity | INT | -1 | -1 | 4 | 4 | +| ss_wholesale_cost | FLOAT | -1 | -1 | 4 | 4 | +| ss_list_price | FLOAT | -1 | -1 | 4 | 4 | +| ss_sales_price | FLOAT | -1 | -1 | 4 | 4 | +| ss_ext_discount_amt | FLOAT | -1 | -1 | 4 | 4 | +| ss_ext_sales_price | FLOAT | -1 | -1 | 4 | 4 | +| ss_ext_wholesale_cost | FLOAT | -1 | -1 | 4 | 4 | +| ss_ext_list_price | FLOAT | -1 | -1 | 4 | 4 | +| ss_ext_tax | FLOAT | -1 | -1 | 4 | 4 | +| ss_coupon_amt | FLOAT | -1 | -1 | 4 | 4 | +| ss_net_paid | FLOAT | -1 | -1 | 4 | 4 | +| ss_net_paid_inc_tax | FLOAT | -1 | -1 | 4 | 4 | +| ss_net_profit | FLOAT | -1 | -1 | 4 | 4 | ++-----------------------+-------+------------------+--------+----------+----------+ +</codeblock> + + <p> + The following examples show the output of the <codeph>SHOW COLUMN STATS</codeph> statement for some tables, + after the <codeph>COMPUTE STATS</codeph> statement is run. Now most of the <codeph>-1</codeph> values are + changed to reflect the actual table data. The <codeph>#Nulls</codeph> column remains <codeph>-1</codeph> + because Impala does not use the number of <codeph>NULL</codeph> values to influence query planning. + </p> + +<codeblock>compute stats customer; ++------------------------------------------+ +| summary | ++------------------------------------------+ +| Updated 1 partition(s) and 18 column(s). | ++------------------------------------------+ + +compute stats store_sales; ++------------------------------------------+ +| summary | ++------------------------------------------+ +| Updated 1 partition(s) and 23 column(s). | ++------------------------------------------+ + +show column stats customer; ++------------------------+--------+------------------+--------+----------+--------+ +| Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size ++------------------------+--------+------------------+--------+----------+--------+ +| c_customer_sk | INT | 139017 | -1 | 4 | 4 | +| c_customer_id | STRING | 111904 | -1 | 16 | 16 | +| c_current_cdemo_sk | INT | 95837 | -1 | 4 | 4 | +| c_current_hdemo_sk | INT | 8097 | -1 | 4 | 4 | +| c_current_addr_sk | INT | 57334 | -1 | 4 | 4 | +| c_first_shipto_date_sk | INT | 4374 | -1 | 4 | 4 | +| c_first_sales_date_sk | INT | 4409 | -1 | 4 | 4 | +| c_salutation | STRING | 7 | -1 | 4 | 3.1308 | +| c_first_name | STRING | 3887 | -1 | 11 | 5.6356 | +| c_last_name | STRING | 4739 | -1 | 13 | 5.9106 | +| c_preferred_cust_flag | STRING | 3 | -1 | 1 | 0.9656 | +| c_birth_day | INT | 31 | -1 | 4 | 4 | +| c_birth_month | INT | 12 | -1 | 4 | 4 | +| c_birth_year | INT | 71 | -1 | 4 | 4 | +| c_birth_country | STRING | 205 | -1 | 20 | 8.4001 | +| c_login | STRING | 1 | -1 | 0 | 0 | +| c_email_address | STRING | 94492 | -1 | 46 | 26.485 | +| c_last_review_date | STRING | 349 | -1 | 7 | 6.7561 | ++------------------------+--------+------------------+--------+----------+--------+ + +show column stats store_sales; ++-----------------------+-------+------------------+--------+----------+----------+ +| Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size | ++-----------------------+-------+------------------+--------+----------+----------+ +| ss_sold_date_sk | INT | 4395 | -1 | 4 | 4 | +| ss_sold_time_sk | INT | 63617 | -1 | 4 | 4 | +| ss_item_sk | INT | 19463 | -1 | 4 | 4 | +| ss_customer_sk | INT | 122720 | -1 | 4 | 4 | +| ss_cdemo_sk | INT | 242982 | -1 | 4 | 4 | +| ss_hdemo_sk | INT | 8097 | -1 | 4 | 4 | +| ss_addr_sk | INT | 70770 | -1 | 4 | 4 | +| ss_store_sk | INT | 6 | -1 | 4 | 4 | +| ss_promo_sk | INT | 355 | -1 | 4 | 4 | +| ss_ticket_number | INT | 304098 | -1 | 4 | 4 | +| ss_quantity | INT | 105 | -1 | 4 | 4 | +| ss_wholesale_cost | FLOAT | 9600 | -1 | 4 | 4 | +| ss_list_price | FLOAT | 22191 | -1 | 4 | 4 | +| ss_sales_price | FLOAT | 20693 | -1 | 4 | 4 | +| ss_ext_discount_amt | FLOAT | 228141 | -1 | 4 | 4 | +| ss_ext_sales_price | FLOAT | 433550 | -1 | 4 | 4 | +| ss_ext_wholesale_cost | FLOAT | 406291 | -1 | 4 | 4 | +| ss_ext_list_price | FLOAT | 574871 | -1 | 4 | 4 | +| ss_ext_tax | FLOAT | 91806 | -1 | 4 | 4 | +| ss_coupon_amt | FLOAT | 228141 | -1 | 4 | 4 | +| ss_net_paid | FLOAT | 493107 | -1 | 4 | 4 | +| ss_net_paid_inc_tax | FLOAT | 653523 | -1 | 4 | 4 | +| ss_net_profit | FLOAT | 611934 | -1 | 4 | 4 | ++-----------------------+-------+------------------+--------+----------+----------+ +</codeblock> + + <p conref="../shared/impala_common.xml#common/permissions_blurb"/> + <p rev="CDH-19187"> + The user ID that the <cmdname>impalad</cmdname> daemon runs under, + typically the <codeph>impala</codeph> user, must have read and execute + permissions for all directories that are part of the table. + (A table could span multiple different HDFS directories if it is partitioned. + The directories could be widely scattered because a partition can reside + in an arbitrary HDFS directory based on its <codeph>LOCATION</codeph> attribute.) + The Impala user must also have execute + permission for the database directory, and any parent directories of the database directory in HDFS. + </p> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + <xref href="impala_compute_stats.xml#compute_stats"/>, <xref href="impala_show.xml#show_table_stats"/> + </p> + + <p> + See <xref href="impala_perf_stats.xml#perf_stats"/> for usage information and examples. + </p> + </conbody> + </concept> + + <concept rev="1.4.0" id="show_partitions"> + + <title>SHOW PARTITIONS Statement</title> + <prolog> + <metadata> + <data name="Category" value="Schemas"/> + <!-- At some point, need to figure out categories related to partitioning. (Partitioned Tables etc.) --> + </metadata> + </prolog> + + <conbody> + + <p> + <codeph>SHOW PARTITIONS</codeph> displays information about each partition for a partitioned table. (The + output is the same as the <codeph>SHOW TABLE STATS</codeph> statement, but <codeph>SHOW PARTITIONS</codeph> + only works on a partitioned table.) Because it displays table statistics for all partitions, the output is + more informative if you have run the <codeph>COMPUTE STATS</codeph> statement after creating all the + partitions. See <xref href="impala_compute_stats.xml#compute_stats"/> for details. For example, on a + <codeph>CENSUS</codeph> table partitioned on the <codeph>YEAR</codeph> column: + </p> + + <p conref="../shared/impala_common.xml#common/security_blurb"/> + + <p conref="../shared/impala_common.xml#common/show_security"/> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + +<codeblock rev="1.4.0">[localhost:21000] > show partitions census; ++-------+-------+--------+------+---------+ +| year | #Rows | #Files | Size | Format | ++-------+-------+--------+------+---------+ +| 2000 | -1 | 0 | 0B | TEXT | +| 2004 | -1 | 0 | 0B | TEXT | +| 2008 | -1 | 0 | 0B | TEXT | +| 2010 | -1 | 0 | 0B | TEXT | +| 2011 | 4 | 1 | 22B | TEXT | +| 2012 | 4 | 1 | 22B | TEXT | +| 2013 | 1 | 1 | 231B | PARQUET | +| Total | 9 | 3 | 275B | | ++-------+-------+--------+------+---------+ +</codeblock> + + <p conref="../shared/impala_common.xml#common/permissions_blurb"/> + <p rev="CDH-19187"> + The user ID that the <cmdname>impalad</cmdname> daemon runs under, + typically the <codeph>impala</codeph> user, must have read and execute + permissions for all directories that are part of the table. + (A table could span multiple different HDFS directories if it is partitioned. + The directories could be widely scattered because a partition can reside + in an arbitrary HDFS directory based on its <codeph>LOCATION</codeph> attribute.) + The Impala user must also have execute + permission for the database directory, and any parent directories of the database directory in HDFS. + </p> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + See <xref href="impala_perf_stats.xml#perf_stats"/> for usage information and examples. + </p> + + <p> + <xref href="impala_show.xml#show_table_stats"/>, <xref href="impala_partitioning.xml#partitioning"/> + </p> + </conbody> + </concept> + + <concept rev="1.3.0" id="show_functions"> + + <title>SHOW FUNCTIONS Statement</title> + <prolog> + <metadata> + <data name="Category" value="Impala Functions"/> + <data name="Category" value="UDFs"/> + </metadata> + </prolog> + + <conbody> + + <p> + By default, <codeph>SHOW FUNCTIONS</codeph> displays user-defined functions (UDFs) and <codeph>SHOW + AGGREGATE FUNCTIONS</codeph> displays user-defined aggregate functions (UDAFs) associated with a particular + database. The output from <codeph>SHOW FUNCTIONS</codeph> includes the argument signature of each function. + You specify this argument signature as part of the <codeph>DROP FUNCTION</codeph> statement. You might have + several UDFs with the same name, each accepting different argument data types. + </p> + + <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> + + <p rev="2.5.0"> + In <keyword keyref="impala25_full"/> and higher, the <codeph>SHOW FUNCTIONS</codeph> output includes + a new column, labelled <codeph>is persistent</codeph>. This property is <codeph>true</codeph> for + Impala built-in functions, C++ UDFs, and Java UDFs created using the new <codeph>CREATE FUNCTION</codeph> + syntax with no signature. It is <codeph>false</codeph> for Java UDFs created using the old + <codeph>CREATE FUNCTION</codeph> syntax that includes the types for the arguments and return value. + Any functions with <codeph>false</codeph> shown for this property must be created again by the + <codeph>CREATE FUNCTION</codeph> statement each time the Impala catalog server is restarted. + See <codeph>CREATE FUNCTION</codeph> for information on switching to the new syntax, so that + Java UDFs are preserved across restarts. Java UDFs that are persisted this way are also easier + to share across Impala and Hive. + </p> + + <p conref="../shared/impala_common.xml#common/security_blurb"/> + + <p conref="../shared/impala_common.xml#common/show_security"/> + + <p conref="../shared/impala_common.xml#common/permissions_blurb_no"/> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + + <p> + To display Impala built-in functions, specify the special database name <codeph>_impala_builtins</codeph>: + </p> + +<codeblock rev="2.5.0">show functions in _impala_builtins; ++--------------+-------------------------------------------------+-------------+---------------+ +| return type | signature | binary type | is persistent | ++--------------+-------------------------------------------------+-------------+---------------+ +| BIGINT | abs(BIGINT) | BUILTIN | true | +| DECIMAL(*,*) | abs(DECIMAL(*,*)) | BUILTIN | true | +| DOUBLE | abs(DOUBLE) | BUILTIN | true | +| FLOAT | abs(FLOAT) | BUILTIN | true | ++----------------+----------------------------------------+ +... + +show functions in _impala_builtins like '*week*'; ++-------------+------------------------------+-------------+---------------+ +| return type | signature | binary type | is persistent | ++-------------+------------------------------+-------------+---------------+ +| INT | dayofweek(TIMESTAMP) | BUILTIN | true | +| INT | weekofyear(TIMESTAMP) | BUILTIN | true | +| TIMESTAMP | weeks_add(TIMESTAMP, BIGINT) | BUILTIN | true | +| TIMESTAMP | weeks_add(TIMESTAMP, INT) | BUILTIN | true | +| TIMESTAMP | weeks_sub(TIMESTAMP, BIGINT) | BUILTIN | true | +| TIMESTAMP | weeks_sub(TIMESTAMP, INT) | BUILTIN | true | ++-------------+------------------------------+-------------+---------------+ +</codeblock> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + <xref href="impala_functions_overview.xml#functions"/>, <xref href="impala_functions.xml#builtins"/>, + <xref href="impala_udf.xml#udfs"/>, + <xref href="impala_show.xml#show_databases"/>, + <xref href="impala_show.xml#show_tables"/> + </p> + </conbody> + </concept> + + <concept rev="someday" audience="Cloudera" id="show_data_sources"> + + <title>SHOW DATA SOURCES Statement (CDH x.y and later only)</title> + + <conbody> + + <p> + <codeph>SHOW DATA SOURCES</codeph> lists the external data sources defined by the <codeph>CREATE DATA + SOURCE</codeph> statement. To show only those names matching a pattern, use the <codeph>LIKE</codeph> + clause with asterisks for wildcards, for example <codeph>SHOW DATA SOURCES LIKE '*sql*'</codeph>. These + data sources are global, not associated with a specific Impala database, so there is no <codeph>IN</codeph> + clause as in most other kinds of objects. + </p> + +<!-- +<p conref="../shared/impala_common.xml#common/example_blurb"/> +<codeblock>To do: construct example for SHOW DATA SOURCES when that statement is externalized</codeblock> +--> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p></p> + </conbody> + </concept> +</concept>
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/3be0f122/docs/topics/impala_smallint.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_smallint.xml b/docs/topics/impala_smallint.xml new file mode 100644 index 0000000..1ed95e6 --- /dev/null +++ b/docs/topics/impala_smallint.xml @@ -0,0 +1,101 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!DOCTYPE concept PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> +<concept id="smallint"> + + <title>SMALLINT Data Type</title> + <titlealts audience="PDF"><navtitle>SMALLINT</navtitle></titlealts> + <prolog> + <metadata> + <data name="Category" value="Impala"/> + <data name="Category" value="Impala Data Types"/> + <data name="Category" value="SQL"/> + <data name="Category" value="Data Analysts"/> + <data name="Category" value="Developers"/> + <data name="Category" value="Schemas"/> + </metadata> + </prolog> + + <conbody> + + <p> + A 2-byte integer data type used in <codeph>CREATE TABLE</codeph> and <codeph>ALTER TABLE</codeph> statements. + </p> + + <p conref="../shared/impala_common.xml#common/syntax_blurb"/> + + <p> + In the column definition of a <codeph>CREATE TABLE</codeph> statement: + </p> + +<codeblock><varname>column_name</varname> SMALLINT</codeblock> + + <p> + <b>Range:</b> -32768 .. 32767. There is no <codeph>UNSIGNED</codeph> subtype. + </p> + + <p> + <b>Conversions:</b> Impala automatically converts to a larger integer type (<codeph>INT</codeph> or + <codeph>BIGINT</codeph>) or a floating-point type (<codeph>FLOAT</codeph> or <codeph>DOUBLE</codeph>) + automatically. Use <codeph>CAST()</codeph> to convert to <codeph>TINYINT</codeph>, <codeph>STRING</codeph>, + or <codeph>TIMESTAMP</codeph>. + <ph conref="../shared/impala_common.xml#common/cast_int_to_timestamp"/> + </p> + + <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> + + <p> + For a convenient and automated way to check the bounds of the <codeph>SMALLINT</codeph> type, call the + functions <codeph>MIN_SMALLINT()</codeph> and <codeph>MAX_SMALLINT()</codeph>. + </p> + + <p> + If an integer value is too large to be represented as a <codeph>SMALLINT</codeph>, use an + <codeph>INT</codeph> instead. + </p> + + <p conref="../shared/impala_common.xml#common/null_bad_numeric_cast"/> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + +<codeblock>CREATE TABLE t1 (x SMALLINT); +SELECT CAST(1000 AS SMALLINT); +</codeblock> + + <p conref="../shared/impala_common.xml#common/parquet_blurb"/> + +<!-- Duplicated under TINYINT and SMALLINT. Turn into a conref in both places. --> + + <p rev="1.4.0"> + Physically, Parquet files represent <codeph>TINYINT</codeph> and <codeph>SMALLINT</codeph> values as 32-bit + integers. Although Impala rejects attempts to insert out-of-range values into such columns, if you create a + new table with the <codeph>CREATE TABLE ... LIKE PARQUET</codeph> syntax, any <codeph>TINYINT</codeph> or + <codeph>SMALLINT</codeph> columns in the original table turn into <codeph>INT</codeph> columns in the new + table. + </p> + + <p conref="../shared/impala_common.xml#common/partitioning_good"/> + + <p conref="../shared/impala_common.xml#common/hbase_ok"/> + + <p conref="../shared/impala_common.xml#common/text_bulky"/> + +<!-- <p conref="../shared/impala_common.xml#common/compatibility_blurb"/> --> + + <p conref="../shared/impala_common.xml#common/internals_2_bytes"/> + + <p conref="../shared/impala_common.xml#common/added_forever"/> + + <p conref="../shared/impala_common.xml#common/column_stats_constant"/> + +<!-- <p conref="../shared/impala_common.xml#common/restrictions_blurb"/> --> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + <xref href="impala_literals.xml#numeric_literals"/>, <xref href="impala_tinyint.xml#tinyint"/>, + <xref href="impala_smallint.xml#smallint"/>, <xref href="impala_int.xml#int"/>, + <xref href="impala_bigint.xml#bigint"/>, <xref href="impala_decimal.xml#decimal"/>, + <xref href="impala_math_functions.xml#math_functions"/> + </p> + </conbody> +</concept> http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/3be0f122/docs/topics/impala_ssl.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_ssl.xml b/docs/topics/impala_ssl.xml new file mode 100644 index 0000000..1e0f2da --- /dev/null +++ b/docs/topics/impala_ssl.xml @@ -0,0 +1,267 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!DOCTYPE concept PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> +<concept id="ssl"> + + <title id="tls">Configuring TLS/SSL for Impala</title> + + <prolog> + <metadata> + <data name="Category" value="Impala"/> + <data name="Category" value="Security"/> + <data name="Category" value="SSL"/> + <data name="Category" value="Encryption"/> + <data name="Category" value="Configuring"/> + <data name="Category" value="Administrators"/> + </metadata> + </prolog> + + <conbody> + + <p> + <indexterm audience="Cloudera">SSL</indexterm> + Impala supports TLS/SSL network encryption, between Impala and client programs, and between the Impala-related daemons running on + different nodes in the cluster. This feature is important when you also use other features such as Kerberos authentication or Sentry + authorization, where credentials are being transmitted back and forth. + <!-- Formerly: conref="../shared/CDHVariables.xml#xd_583c10bfdbd326ba-3ca24a24-13d80143249- -7f9a/CMCDH_EitherOK" --> + <note type="important" id="CMCDH_EitherOK"> + <ul id="ul_e2s_bcd_np"> + <li>You can use either Cloudera Manager or the following command-line instructions + to complete this configuration.</li> + <!-- Took out another too-specific conref, to the CDH minor version also in CDHVariables.xml. --> + <li>This information applies specifically to the version of Impala shown in the HTML page header + or on the PDF title page. + If you use an earlier version of CDH, see the documentation for that version located at + <xref href="http://www.cloudera.com/content/support/en/documentation.html"; format="html" scope="external">Cloudera Documentation</xref>.</li> + </ul></note> + /> + </p> + + </conbody> + + <concept id="concept_gnk_2tt_qp"> + + <title>Using Cloudera Manager</title> + + <prolog> + <metadata> + <data name="Category" value="Cloudera Manager"/> + </metadata> + </prolog> + + <conbody> + + <p> + To configure Impala to listen for Beeswax and HiveServer2 requests on TLS/SSL-secured ports: + <ol id="ol_rnf_ftt_qp"> + <li> + Open the Cloudera Manager Admin Console and go to the <uicontrol>Impala</uicontrol> service. + </li> + + <!-- Formerly: conref="../shared/cm_common_elements.xml#cm/config_edit" --> + <li>Click the <uicontrol>Configuration</uicontrol> tab.</li> + + <li> + Select <menucascade><uicontrol>Scope</uicontrol><uicontrol>Impala (Service-Wide)</uicontrol></menucascade>. + </li> + + <li> + Select <menucascade><uicontrol>Category</uicontrol><uicontrol>Security</uicontrol></menucascade>. + </li> + + <li> + Edit the following properties: + <table frame="all" + id="table_drf_ftt_qp"> + <title>Impala SSL Properties</title> + <tgroup cols="2"> + <colspec colname="c1" colnum="1" colwidth="1*"/> + <colspec colname="c2" colnum="2" colwidth="2.5*"/> + <thead> + <row> + <entry> + Property + </entry> + <entry> + Description + </entry> + </row> + </thead> + <tbody> + <row> + <entry> + <b>Enable TLS/SSL for Impala Client Services</b> + </entry> + <entry> + Encrypt communication between clients (like ODBC, JDBC, and the Impala shell) and the Impala daemon using Transport + Layer Security (TLS) (formerly known as Secure Socket Layer (SSL)). + </entry> + </row> + <row> + <entry> + <b>SSL/TLS Certificate for Clients</b> + </entry> + <entry> + Local path to the X509 certificate that identifies the Impala daemon to clients during TLS/SSL connections. This + file must be in PEM format. + </entry> + </row> + <row> + <entry> + <b>SSL/TLS Private Key for Clients</b> + </entry> + <entry> + Local path to the private key that matches the certificate specified in the Certificate for Clients. This file must be + in PEM format. + </entry> + </row> + <row> + <entry> + <b>SSL/TLS Private Key Password for Clients</b> + </entry> + <entry> + A shell command for Impala to run on startup to retrieve the password for a password-protected private key file. + The output of the command is truncated to a maximum of 1024 bytes, and any trailing whitespace (such as spaces + or newline characters) is trimmed. If the command exits with an error, Impala does not start. If the password + is incorrect, clients cannot connect to the server regardless of whether the public key is correct. + </entry> + </row> + <row> + <entry> + <b>SSL/TLS CA Certificate</b> + </entry> + <entry> + Must be specified for TLS/SSL encryption to be enabled for communication + between internal Impala components. + </entry> + </row> + <row> + <entry> + <b>SSL/TLS Certificate for <varname>Impala component</varname> Webserver</b> + </entry> + <entry> + There are three of these configuration settings, one each for <q>Impala Daemon</q>, + <q>Catalog Server</q>, and <q>Statestore</q>. + Each of these Impala components has its own internal web server that powers the + associated web UI with diagnostic information. + The configuration setting represents the local path to the X509 certificate that + identifies the web server to clients during TLS/SSL connections. This + file must be in PEM format. + </entry> + </row> + </tbody> + </tgroup> + </table> + </li> + + <!-- Formerly: conref="../shared/cm_common_elements.xml#cm/save_changes_short" --> + <li>Click <uicontrol>Save Changes</uicontrol> to commit the changes.</li> + + <li> + Restart the Impala service. + </li> + </ol> + </p> + + <p> + For information on configuring TLS/SSL communication with the <codeph>impala-shell</codeph> interpreter, see + <xref href="#concept_q1p_j2d_rp/secref"/>. + </p> + + </conbody> + + </concept> + + <concept id="concept_q1p_j2d_rp"> + + <title>Using the Command Line</title> + + <conbody> + +<!-- +Info from Henry, from https://docs.google.com/a/cloudera.com/document/d/1u00CJ8WRzXR-1AK_WnQlR6LMtY-7Rc3eHaKNgw3IZvA/edit +--> + + <p> + To enable SSL for when client applications connect to Impala, add both of the following flags to the <cmdname>impalad</cmdname> startup options: + </p> + + <ul id="ul_i2p_m2d_rp"> + <li> + <codeph>--ssl_server_certificate</codeph>: the full path to the server certificate, on the local filesystem. + </li> + + <li> + <codeph>--ssl_private_key</codeph>: the full path to the server private key, on the local filesystem. + </li> + </ul> + + <p rev="2.3.0"> + In <keyword keyref="impala23_full"/> and higher, Impala can also use SSL for its own internal communication between the + <cmdname>impalad</cmdname>, <codeph>statestored</codeph>, and <codeph>catalogd</codeph> daemons. + To enable this additional SSL encryption, set the <codeph>--ssl_server_certificate</codeph> + and <codeph>--ssl_private_key</codeph> flags in the startup options for + <cmdname>impalad</cmdname>, <cmdname>catalogd</cmdname>, and <cmdname>statestored</cmdname>, + and also add the <codeph>--ssl_client_ca_certificate</codeph> flag for all three of those daemons. + </p> + + <note conref="../shared/impala_common.xml#common/impala_kerberos_ssl_caveat"/> + + <p> + If either of these flags are set, both must be set. In that case, Impala starts listening for Beeswax and HiveServer2 requests on + SSL-secured ports only. (The port numbers stay the same; see <xref href="impala_ports.xml#ports"/> for details.) + </p> + + <p> + Since Impala uses passphrase-less certificates in PEM format, you can reuse a host's existing Java keystore by converting it to the + PEM format. For instructions, see + <xref audience="integrated" href="cm_sg_openssl_jks.xml#concept_ek3_sdl_rp"/><xref audience="standalone" href="http://www.cloudera.com/documentation/enterprise/latest/topics/cm_sg_openssl_jks.html"; scope="external" format="html"/>. + </p> + + <section id="secref"> + + <title>Configuring TLS/SSL Communication for the Impala Shell</title> + + <p> + Typically, a client program has corresponding configuration properties in Cloudera Manager to verify that it is connecting to the + right server. For example, with SSL enabled for Impala, you use the following options when starting the + <cmdname>impala-shell</cmdname> interpreter: + </p> + + <ul id="ul_kgp_m2d_rp"> + <li> + <codeph>--ssl</codeph>: enables TLS/SSL for <cmdname>impala-shell</cmdname>. + </li> + + <li> + <codeph>--ca_cert</codeph>: the local pathname pointing to the third-party CA certificate, or to a copy of the server + certificate for self-signed server certificates. + </li> + </ul> + + <p> + If <codeph>--ca_cert</codeph> is not set, <cmdname>impala-shell</cmdname> enables TLS/SSL, but does not validate the server + certificate. This is useful for connecting to a known-good Impala that is only running over TLS/SSL, when a copy of the + certificate is not available (such as when debugging customer installations). + </p> + + </section> + + </conbody> + + </concept> + + <concept id="ssl_jdbc_odbc"> + <title>Using TLS/SSL with Business Intelligence Tools</title> + <conbody> + <p> + You can use Kerberos authentication, TLS/SSL encryption, or both to secure + connections from JDBC and ODBC applications to Impala. + See <xref href="impala_jdbc.xml#impala_jdbc"/> and <xref href="impala_odbc.xml#impala_odbc"/> + for details. + </p> + + <p conref="../shared/impala_common.xml#common/hive_jdbc_ssl_kerberos_caveat"/> + </conbody> + </concept> + +</concept> http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/3be0f122/docs/topics/impala_stddev.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_stddev.xml b/docs/topics/impala_stddev.xml new file mode 100644 index 0000000..9cd4126 --- /dev/null +++ b/docs/topics/impala_stddev.xml @@ -0,0 +1,118 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!DOCTYPE concept PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> +<concept rev="1.4" id="stddev"> + + <title>STDDEV, STDDEV_SAMP, STDDEV_POP Functions</title> + <titlealts audience="PDF"><navtitle>STDDEV, STDDEV_SAMP, STDDEV_POP</navtitle></titlealts> + <prolog> + <metadata> + <data name="Category" value="Impala"/> + <data name="Category" value="SQL"/> + <data name="Category" value="Impala Functions"/> + <data name="Category" value="Aggregate Functions"/> + <data name="Category" value="Querying"/> + <data name="Category" value="Developers"/> + <data name="Category" value="Data Analysts"/> + </metadata> + </prolog> + + <conbody> + + <p> + <indexterm audience="Cloudera">stddev() function</indexterm> + <indexterm audience="Cloudera">stddev_samp() function</indexterm> + <indexterm audience="Cloudera">stddev_pop() function</indexterm> + An aggregate function that + <xref href="http://en.wikipedia.org/wiki/Standard_deviation"; scope="external" format="html">standard + deviation</xref> of a set of numbers. + </p> + + <p conref="../shared/impala_common.xml#common/syntax_blurb"/> + +<codeblock>{ STDDEV | STDDEV_SAMP | STDDEV_POP } ([DISTINCT | ALL] <varname>expression</varname>)</codeblock> + + <p> + This function works with any numeric data type. + </p> + + <p conref="../shared/impala_common.xml#common/former_odd_return_type_string"/> + + <p> + This function is typically used in mathematical formulas related to probability distributions. + </p> + + <p> + The <codeph>STDDEV_POP()</codeph> and <codeph>STDDEV_SAMP()</codeph> functions compute the population + standard deviation and sample standard deviation, respectively, of the input values. + (<codeph>STDDEV()</codeph> is an alias for <codeph>STDDEV_SAMP()</codeph>.) Both functions evaluate all input + rows matched by the query. The difference is that <codeph>STDDEV_SAMP()</codeph> is scaled by + <codeph>1/(N-1)</codeph> while <codeph>STDDEV_POP()</codeph> is scaled by <codeph>1/N</codeph>. + </p> + + <p> + If no input rows match the query, the result of any of these functions is <codeph>NULL</codeph>. If a single + input row matches the query, the result of any of these functions is <codeph>"0.0"</codeph>. + </p> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + + <p> + This example demonstrates how <codeph>STDDEV()</codeph> and <codeph>STDDEV_SAMP()</codeph> return the same + result, while <codeph>STDDEV_POP()</codeph> uses a slightly different calculation to reflect that the input + data is considered part of a larger <q>population</q>. + </p> + +<codeblock>[localhost:21000] > select stddev(score) from test_scores; ++---------------+ +| stddev(score) | ++---------------+ +| 28.5 | ++---------------+ +[localhost:21000] > select stddev_samp(score) from test_scores; ++--------------------+ +| stddev_samp(score) | ++--------------------+ +| 28.5 | ++--------------------+ +[localhost:21000] > select stddev_pop(score) from test_scores; ++-------------------+ +| stddev_pop(score) | ++-------------------+ +| 28.4858 | ++-------------------+ +</codeblock> + + <p> + This example demonstrates that, because the return value of these aggregate functions is a + <codeph>STRING</codeph>, you must currently convert the result with <codeph>CAST</codeph>. + </p> + +<codeblock>[localhost:21000] > create table score_stats as select cast(stddev(score) as decimal(7,4)) `standard_deviation`, cast(variance(score) as decimal(7,4)) `variance` from test_scores; ++-------------------+ +| summary | ++-------------------+ +| Inserted 1 row(s) | ++-------------------+ +[localhost:21000] > desc score_stats; ++--------------------+--------------+---------+ +| name | type | comment | ++--------------------+--------------+---------+ +| standard_deviation | decimal(7,4) | | +| variance | decimal(7,4) | | ++--------------------+--------------+---------+ +</codeblock> + + <p conref="../shared/impala_common.xml#common/restrictions_blurb"/> + + <p conref="../shared/impala_common.xml#common/analytic_not_allowed_caveat"/> + + <p conref="../shared/impala_common.xml#common/related_info"/> + + <p> + The <codeph>STDDEV()</codeph>, <codeph>STDDEV_POP()</codeph>, and <codeph>STDDEV_SAMP()</codeph> functions + compute the standard deviation (square root of the variance) based on the results of + <codeph>VARIANCE()</codeph>, <codeph>VARIANCE_POP()</codeph>, and <codeph>VARIANCE_SAMP()</codeph> + respectively. See <xref href="impala_variance.xml#variance"/> for details about the variance property. + </p> + </conbody> +</concept>
