Repository: drill Updated Branches: refs/heads/gh-pages 0ce771f06 -> 49e50be68
edit to ctas doc to include permissions option per DRILL-5391 Project: http://git-wip-us.apache.org/repos/asf/drill/repo Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/49e50be6 Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/49e50be6 Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/49e50be6 Branch: refs/heads/gh-pages Commit: 49e50be687eef355273ddb29d5a4c2d175e3c5d1 Parents: 0ce771f Author: Bridget Bevens <bbev...@maprtech.com> Authored: Wed Aug 9 15:58:32 2017 -0700 Committer: Bridget Bevens <bbev...@maprtech.com> Committed: Wed Aug 9 15:58:32 2017 -0700 ---------------------------------------------------------------------- .../sql-commands/030-create-table-as.md | 57 ++++++++++++++------ 1 file changed, 41 insertions(+), 16 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/drill/blob/49e50be6/_docs/sql-reference/sql-commands/030-create-table-as.md ---------------------------------------------------------------------- diff --git a/_docs/sql-reference/sql-commands/030-create-table-as.md b/_docs/sql-reference/sql-commands/030-create-table-as.md index 41fae14..6866988 100644 --- a/_docs/sql-reference/sql-commands/030-create-table-as.md +++ b/_docs/sql-reference/sql-commands/030-create-table-as.md @@ -1,35 +1,60 @@ --- title: "CREATE TABLE AS (CTAS)" -date: +date: 2017-08-09 22:58:36 UTC parent: "SQL Commands" --- -You can create tables in Drill by using the CTAS command. +Use the CTAS command to create tables in Drill. ## Syntax - CREATE TABLE name [ (column list) ] AS query; + CREATE TABLE name [ (column list) ] AS query; -*name* is a unique directory name, optionally prefaced by a storage plugin name, such as dfs, and a workspace, such as tmp using [dot notation]({{site.baseurl}}/docs/workspaces). -*column list* is an optional list of column names or aliases in the new table. -*query* is a SELECT statement that needs to include aliases for ambiguous column names, such as COLUMNS[0]. Using SELECT * is [not recommended]({{site.baseurl}}/docs/text-files-csv-tsv-psv/#tips-for-performant-querying) when selecting CSV, TSV, and PSV data. +## Parameters +*name* +A unique directory name, optionally prefaced by a storage plugin name, such as dfs, and a workspace, such as tmp using [dot notation]({{site.baseurl}}/docs/workspaces). + +*column list* +An optional list of column names or aliases in the new table. + +*query* +A SELECT statement that needs to include aliases for ambiguous column names, such as COLUMNS[0]. Using SELECT * is [not recommended]({{site.baseurl}}/docs/text-files-csv-tsv-psv/#tips-for-performant-querying) when selecting CSV, TSV, and PSV data. -You can use the [PARTITION BY]({{site.baseurl}}/docs/partition-by-clause) clause in a CTAS command. +## Usage Notes -Drill writes files having names, such as 0_0_0.parquet, to the directory named in the CTAS command or to the workspace that is in use when you run the CTAS statement. You query the directory as you would query a table. +- You can use the [PARTITION BY]({{site.baseurl}}/docs/partition-by-clause) clause in a CTAS command. -The following command writes Parquet data from `nation.parquet`, installed with Drill, to the `/tmp/name_key` directory. +- Drill writes files having names, such as 0_0_0.parquet, to the directory named in the CTAS command or to the workspace that is in use when you run the CTAS statement. You query the directory as you would query a table. - USE dfs; - CREATE TABLE tmp.`name_key` (N_NAME, N_NATIONKEY) AS SELECT N_NATIONKEY, N_NAME FROM dfs.`/Users/drilluser/apache-drill-1.0/sample-data/nation.parquet`; -To query the data, use this command: - SELECT * FROM tmp.`name_key`; + - The following command writes Parquet data from `nation.parquet`, installed with Drill, to the `/tmp/name_key` directory. + + USE dfs; + CREATE TABLE tmp.`name_key` (N_NAME, N_NATIONKEY) AS SELECT N_NATIONKEY, N_NAME FROM dfs.`/Users/drilluser/apache-drill-1.0/sample-data/nation.parquet`; + + + - To query the data, use this command: + + SELECT * FROM tmp.`name_key`; + + + + - This example writes a JSON table to the `/tmp/by_yr` directory that contains [Google Ngram data]({{site.baseurl}}/docs/partition-by-clause/#partioning-example). + + Use dfs.tmp; + CREATE TABLE by_yr (yr, ngram, occurrances) AS SELECT COLUMNS[0] ngram, COLUMNS[1] yr, COLUMNS[2] occurrances FROM `googlebooks-eng-all-5gram-20120701-zo.tsv` WHERE (columns[1] = '1993'); + + + +- Drill 1.11 introduces the `exec.persistent_table.umask` option, which enables you to set permissions on tables and directories that result from running the CTAS command. By default, the option is set to 002, which sets the default directory permissions to 775 and default file permissions to 664. Use the [SET]({{site.baseurl}}/docs/set/) command to change the setting for this option at the system or session level, as shown: + + ALTER SYSTEM|SESSION SET `exec.persistent_table.umask` = '000'; + + + + - Setting the option to '000' sets the folder permissions to 777 and the file permissions to 666. This setting gives full access to folders and files when you create a table. -This example writes a JSON table to the `/tmp/by_yr` directory that contains [Google Ngram data]({{site.baseurl}}/docs/partition-by-clause/#partioning-example). - Use dfs.tmp; - CREATE TABLE by_yr (yr, ngram, occurrances) AS SELECT COLUMNS[0] ngram, COLUMNS[1] yr, COLUMNS[2] occurrances FROM `googlebooks-eng-all-5gram-20120701-zo.tsv` WHERE (columns[1] = '1993'); ## Setting the Storage Format
