{"id":1494,"date":"2020-02-29T23:19:42","date_gmt":"2020-02-29T14:19:42","guid":{"rendered":"https:\/\/oboki.net\/?p=1494"},"modified":"2020-03-09T23:35:01","modified_gmt":"2020-03-09T14:35:01","slug":"all_tab_columns-for-impala-hive","status":"publish","type":"post","link":"https:\/\/oboki.net\/workspace\/data-engineering\/database\/impala\/all_tab_columns-for-impala-hive\/","title":{"rendered":"[Impala] ALL_TAB_COLUMNS for Impala (Hive)"},"content":{"rendered":"<p>\ud604\uc7ac \uc5c5\ubb34\uc5d0\uc11c\ub294 \uc784\ud314\ub77c\ub97c \uc8fc\ub825 \ub370\uc774\ud130\ubca0\uc774\uc2a4\ub85c \ud65c\uc6a9\ud558\uace0 \uc788\ub2e4. \uc624\ub77c\ud074 \uc5d1\uc0ac DW\uc5d0\uc11c \ud558\ub461\uc73c\ub85c ETL \ud55c \ub4a4, \uc6b4\uc601 DW\uc5d0\ub294 \ud558\uae30 \ubd80\ub2f4\uc2a4\ub7ec\uc6b4 \ud5e4\ube44\ud55c \ucffc\ub9ac\ub97c \ud558\ub294\ub370\uc5d0 \uc0ac\uc6a9\ud55c\ub2e4.<\/p>\n<p>\uc6d0\ucc9c\uc774 \uc624\ub77c\ud074 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc778 \ud0d3\uc5d0 \uc18c\uc2a4 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc778 \uc624\ub77c\ud074\uacfc \uc8fc\uae30\uc801\uc73c\ub85c \uc2a4\ud0a4\ub9c8\ub97c \ub9de\ucdb0\uc8fc\uc5b4\uc57c \ud558\ub294\ub370, \uc624\ub77c\ud074\uc5d0\ub294 <code>ALL_TABLES<\/code>, <code>ALL_TAB_COLUMNS<\/code> \uc640 \uac19\uc740 \uc2dc\uc2a4\ud15c \uce74\ud0c8\ub85c\uadf8\uac00 \uc788\uc5b4, \ud14c\uc774\ube14 \uc2a4\ud0a4\ub9c8 \uad00\ub828 \ud544\uc694 \uc815\ubcf4\ub97c \ucffc\ub9ac\ud574\ubcf4\uae30 \uc88b\uc558\ub294\ub370 \uc784\ud314\ub77c\uc5d0\ub294 \uc774\ub7f0 \uc2dc\uc2a4\ud15c \uce74\ud0c8\ub85c\uadf8\uac00 \uae30\ubcf8 \uc81c\uacf5\ub418\uace0 \uc788\uc9c0 \uc54a\uc544 \uba54\ud0c0 \uc870\ud68c\ub97c \ud558\ub294\ub370 \ubc88\uac70\ub85c\uc6c0\uc774 \uc788\uc5b4 \ub450 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uac04\uc758 \uc2a4\ud0a4\ub9c8\ub97c \ube44\uad50\ud574\ubcf4\uae30\uac00 \uc27d\uc9c0 \uc54a\uc558\ub2e4.<\/p>\n<p>\uc774\ub7f0 \uc791\uc5c5\uc744 \ud558\ub294\ub370\uc5d0 \uc788\uc5b4\uc11c HUE\uc758 \ud14c\uc774\ube14 \ube0c\ub77c\uc6b0\uc800\ub97c \uc774\uc6a9\ud574 \uc77c\uc77c\uc774 \ub208\uc73c\ub85c \ube44\uad50\ud560 \uc218\ub294 \uc5c6\ub294 \ub178\ub987\uc774\uace0 Impala\uc5d0\uc11c <code>describe<\/code> \ub97c \uc774\uc6a9\ud55c \uc678\ubd80 \uc791\uc5c5\uc744 \ub9cc\ub4dc\ub294 \uac83\ub3c4 \ud6a8\uc728\uc801\uc774\uc9c0 \uc54a\uc740 \uac83 \uac19\uc544 \ucc3e\uc544\ubcf4\ub2c8, Impala\ub294 \ub0b4\ubd80\uc801\uc73c\ub85c Hive Metastore \ub97c \ud1b5\ud574 \uc2a4\ud0a4\ub9c8\ub97c \uad00\ub9ac\ud558\uace0 \uc788\uc5c8\uace0 Hive \uc5d0\uc11c \uc81c\uacf5\ud558\ub294 \uc2dc\uc2a4\ud15c \uce74\ud0c8\ub85c\uadf8\ub97c \uc774\uc6a9\ud574 \ud544\uc694\ud55c \uc815\ubcf4\ub97c \ucc3e\uc544\ub0bc \uc218 \uc788\uc5c8\ub2e4.<\/p>\n<p>hive repository \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc778 <code>metastore<\/code> \uc758 <code>DBS<\/code>, <code>TBLS<\/code>, <code>SDS<\/code>, <code>PARTITION_KEYS<\/code>, <code>COLUMNS_V2<\/code> \uac00 \ubc14\ub85c \uadf8\uac83\ub4e4\uc774\uc5c8\uace0 \uc774 \ud14c\uc774\ube14\ub4e4\uc744 \ubc14\ud0d5\uc73c\ub85c \ub2e4\uc74c\uacfc \uac19\uc740 VIEW \ub97c \uc0dd\uc131\ud574 \ud65c\uc6a9\ud558\uace0 \uc788\ub2e4.<\/p>\n<ul>\n<li>ALL_TAB_COLUMNS [for Impala]\n<ul>\n<li>OWNER<\/li>\n<li>TABLE_NAME<\/li>\n<li>COLUMN_NAME<\/li>\n<li>DATA_TYPE<\/li>\n<li>COLUMN_ID<\/li>\n<li>IS_PARTKEY<\/li>\n<li>INPUT_FORMAT<\/li>\n<li>DB_NAME<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2>ALL_TAB_COLUMNS for Impala DDL<\/h2>\n<p>\uc784\ud314\ub77c\ub97c \uc6b4\uc601\ud558\ub294\ub370 \uc788\uc5b4\uc11c \uba87\uac00\uc9c0 \ud544\uc694\ud55c \uceec\ub7fc\uc744 \ucd94\uac00\ud574 \uc704\uc640 \uac19\uc774 \uc124\uacc4\ud588\uace0 DDL\uc740 \ub2e4\uc74c\uacfc \uac19\ub2e4. <del>\ub2e4\uc74c SQL\uc740 Impala\uc5d0\uc11c \uc9c0\uc6d0\ud558\ub294 \ubb38\ubc95 \uae30\ubc18\uc73c\ub85c \uc9dc\uc5ec\uc9c4 \uac83\uc73c\ub85c, DBS, TBLS, SDS, PARTITION_KEYS, COLUMNS_V2 \ud14c\uc774\ube14\uc744 Impala \ud14c\uc774\ube14\ub85c \ub3d9\uae30\ud654\ud55c \ub4a4\uc5d0 \ud65c\uc6a9\ud560 \uc218 \uc788\ub2e4. \uac1c\uc778 \ub85c\uceec \ud14c\uc2a4\ud2b8 \ud658\uacbd\uc778 CDH \ub370\ubaa8 \uc774\ubbf8\uc9c0\uc758 MySQL 5.1 \ubc84\uc804\uc5d0\uc11c \ub2e4\uc74c \ucffc\ub9ac\ub97c \ud574\ubcf4\ub2c8 <code>WITH \uc808<\/code>\uacfc <code>ROW_NUMBER()<\/code> \uc640 \uac19\uc740 \uc708\ub3c4\uc6b0 \ucffc\ub9ac\uac00 \uc9c0\uc6d0\ub418\uc9c0 \uc54a\uc544 \ud574\ub2f9 \ubc84\uc804\uc5d0 \ub9de\uac8c \ub2e4\uc2dc \uc791\uc131\ud574\ubd10\uc57c \ud560 \ub4ef \ud558\ub2e4.<\/del><\/p>\n<pre><code class=\"language-sql\">CREATE VIEW ALL_TAB_COLUMNS\n    AS\n  WITH TABS AS (SELECT B.`OWNER`\n                     , B.TBL_NAME AS TABLE_NAME\n                     , CASE C.INPUT_FORMAT\n                          WHEN &#039;org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat&#039; THEN &#039;PARQUET&#039;\n                          WHEN &#039;org.apache.mapreduce.KuduTableInputFormat&#039;                     THEN &#039;KUDU&#039;\n                          WHEN &#039;org.apache.hadoop.mapred.TextInputFormat&#039;                      THEN &#039;TEXT&#039;\n                       END AS INPUT_FORMAT\n                     , A.NAME AS DB_NAME\n                     , B.TBL_ID\n                     , C.CD_ID\n                  FROM DBS  A                        INNER JOIN\n                       TBLS B ON (A.DB_ID = B.DB_ID) INNER JOIN\n                       SDS  C ON (B.SD_ID = C.SD_ID)\n               )\nSELECT A.`OWNER`\n     , A.TABLE_NAME\n     , A.COLUMN_NAME\n     , A.DATA_TYPE\n     , ROW_NUMBER() OVER(PARTITION BY A.`OWNER`, A.TABLE_NAME ORDER BY A.INTEGER_IDX ASC) COLUMN_ID\n     , A.IS_PARTKEY\n     , A.INPUT_FORMAT\n     , A.DB_NAME\n  FROM (SELECT A.`OWNER`\n             , A.TABLE_NAME\n             , B.PKEY_NAME AS COLUMN_NAME\n             , B.PKEY_TYPE AS DATA_TYPE\n             , B.INTEGER_IDX - 100 AS INTEGER_IDX\n             , &#039;Y&#039; IS_PARTKEY\n             , A.INPUT_FORMAT\n             , A.DB_NAME\n          FROM TABS           A INNER JOIN\n               PARTITION_KEYS B ON (A.TBL_ID = B.TBL_ID)\n         UNION ALL\n        SELECT A.`OWNER`\n             , A.TABLE_NAME\n             , B.COLUMN_NAME\n             , B.TYPE_NAME AS DATA_TYPE\n             , B.INTEGER_IDX AS INTEGER_IDX\n             , &#039;N&#039; IS_PARTKEY\n             , A.INPUT_FORMAT\n             , A.DB_NAME\n        FROM TABS       A INNER JOIN\n             COLUMNS_V2 B ON (A.CD_ID = B.CD_ID)\n     ) A<\/code><\/pre>\n<p>\uc784\ud314\ub77c\ub294 \uc624\ub77c\ud074 \ub9cc\ud07c \uc138\ubd80\uc801\uc778 \uc124\uc815\uc774 \uc5c6\uc5b4 \uc704\uc640 \uac19\uc740 \uceec\ub7fc\ub9cc\uc73c\ub85c\ub3c4 \ub2f9\uc7a5\uc740 \uc0ac\uc6a9\ud558\ub294 \ub370\uc5d0 \ud06c\uac8c \ubb34\ub9ac\uac00 \uc5c6\ub294 \uac83 \uac19\uc740\ub370 \uc624\ub77c\ud074\uc758 ALL_TAB_COLUMNS \uc5d0 \ube44\ud558\uba74 \ub9ce\uc774 \ucd08\ub77c\ud55c \uac83 \uac19\uae30\ub3c4.. \ucd94\ud6c4 \ub2e4\ub978 \uc218\uc694\uac00 \uc0dd\uae30\uac8c \ub418\uba74 \uace0\ub3c4\ud654\ub97c \ud574\ubd10\uc57c \uaca0\ub2e4.<\/p>\n<h2>Hive \uc2dc\uc2a4\ud15c \uce74\ud0c8\ub85c\uadf8<\/h2>\n<ul>\n<li>DBS<\/li>\n<\/ul>\n<pre><code class=\"language-sql\">mysql&gt; desc DBS;\n+-----------------+---------------+------+-----+---------+-------+\n| Field           | Type          | Null | Key | Default | Extra |\n+-----------------+---------------+------+-----+---------+-------+\n| DB_ID           | bigint(20)    | NO   | PRI | NULL    |       |\n| DESC            | varchar(4000) | YES  |     | NULL    |       |\n| DB_LOCATION_URI | varchar(4000) | NO   |     | NULL    |       |\n| NAME            | varchar(128)  | YES  | UNI | NULL    |       |\n| OWNER_NAME      | varchar(128)  | YES  |     | NULL    |       |\n| OWNER_TYPE      | varchar(10)   | YES  |     | NULL    |       |\n+-----------------+---------------+------+-----+---------+-------+<\/code><\/pre>\n<ul>\n<li>TBLS<\/li>\n<\/ul>\n<pre><code class=\"language-sql\">mysql&gt; desc TBLS;\n+--------------------+--------------+------+-----+---------+-------+\n| Field              | Type         | Null | Key | Default | Extra |\n+--------------------+--------------+------+-----+---------+-------+\n| TBL_ID             | bigint(20)   | NO   | PRI | NULL    |       |\n| CREATE_TIME        | int(11)      | NO   |     | NULL    |       |\n| DB_ID              | bigint(20)   | YES  | MUL | NULL    |       |\n| LAST_ACCESS_TIME   | int(11)      | NO   |     | NULL    |       |\n| OWNER              | varchar(767) | YES  |     | NULL    |       |\n| RETENTION          | int(11)      | NO   |     | NULL    |       |\n| SD_ID              | bigint(20)   | YES  | MUL | NULL    |       |\n| TBL_NAME           | varchar(128) | YES  | MUL | NULL    |       |\n| TBL_TYPE           | varchar(128) | YES  |     | NULL    |       |\n| VIEW_EXPANDED_TEXT | mediumtext   | YES  |     | NULL    |       |\n| VIEW_ORIGINAL_TEXT | mediumtext   | YES  |     | NULL    |       |\n| LINK_TARGET_ID     | bigint(20)   | YES  | MUL | NULL    |       |\n+--------------------+--------------+------+-----+---------+-------+<\/code><\/pre>\n<ul>\n<li>SDS<\/li>\n<\/ul>\n<pre><code class=\"language-sql\">mysql&gt; desc SDS;\n+---------------------------+---------------+------+-----+---------+-------+\n| Field                     | Type          | Null | Key | Default | Extra |\n+---------------------------+---------------+------+-----+---------+-------+\n| SD_ID                     | bigint(20)    | NO   | PRI | NULL    |       |\n| CD_ID                     | bigint(20)    | YES  | MUL | NULL    |       |\n| INPUT_FORMAT              | varchar(4000) | YES  |     | NULL    |       |\n| IS_COMPRESSED             | bit(1)        | NO   |     | NULL    |       |\n| IS_STOREDASSUBDIRECTORIES | bit(1)        | NO   |     | NULL    |       |\n| LOCATION                  | varchar(4000) | YES  |     | NULL    |       |\n| NUM_BUCKETS               | int(11)       | NO   |     | NULL    |       |\n| OUTPUT_FORMAT             | varchar(4000) | YES  |     | NULL    |       |\n| SERDE_ID                  | bigint(20)    | YES  | MUL | NULL    |       |\n+---------------------------+---------------+------+-----+---------+-------+<\/code><\/pre>\n<ul>\n<li>PARTITION_KEYS<\/li>\n<\/ul>\n<pre><code class=\"language-sql\">mysql&gt; desc PARTITION_KEYS;\n+--------------+---------------+------+-----+---------+-------+\n| Field        | Type          | Null | Key | Default | Extra |\n+--------------+---------------+------+-----+---------+-------+\n| TBL_ID       | bigint(20)    | NO   | PRI | NULL    |       |\n| PKEY_COMMENT | varchar(4000) | YES  |     | NULL    |       |\n| PKEY_NAME    | varchar(128)  | NO   | PRI | NULL    |       |\n| PKEY_TYPE    | varchar(767)  | NO   |     | NULL    |       |\n| INTEGER_IDX  | int(11)       | NO   |     | NULL    |       |\n+--------------+---------------+------+-----+---------+-------+<\/code><\/pre>\n<ul>\n<li>COLUMNS_V2<\/li>\n<\/ul>\n<pre><code class=\"language-sql\">mysql&gt; desc COLUMNS_V2;\n+-------------+---------------+------+-----+---------+-------+\n| Field       | Type          | Null | Key | Default | Extra |\n+-------------+---------------+------+-----+---------+-------+\n| CD_ID       | bigint(20)    | NO   | PRI | NULL    |       |\n| COMMENT     | varchar(256)  | YES  |     | NULL    |       |\n| COLUMN_NAME | varchar(128)  | NO   | PRI | NULL    |       |\n| TYPE_NAME   | varchar(4000) | YES  |     | NULL    |       |\n| INTEGER_IDX | int(11)       | NO   |     | NULL    |       |\n+-------------+---------------+------+-----+---------+-------+<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\ud604\uc7ac \uc5c5\ubb34\uc5d0\uc11c\ub294 \uc784\ud314\ub77c\ub97c \uc8fc\ub825 \ub370\uc774\ud130\ubca0\uc774\uc2a4\ub85c \ud65c\uc6a9\ud558\uace0 \uc788\ub2e4. \uc624\ub77c\ud074 \uc5d1\uc0ac DW\uc5d0\uc11c \ud558\ub461\uc73c\ub85c ETL \ud55c \ub4a4, \uc6b4\uc601 DW\uc5d0\ub294 \ud558\uae30 \ubd80\ub2f4\uc2a4\ub7ec\uc6b4 \ud5e4\ube44\ud55c \ucffc\ub9ac\ub97c \ud558\ub294\ub370\uc5d0 \uc0ac\uc6a9\ud55c\ub2e4. \uc6d0\ucc9c\uc774 \uc624\ub77c\ud074 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc778 \ud0d3\uc5d0 \uc18c\uc2a4 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc778 \uc624\ub77c\ud074\uacfc \uc8fc\uae30\uc801\uc73c\ub85c \uc2a4\ud0a4\ub9c8\ub97c \ub9de\ucdb0\uc8fc\uc5b4\uc57c \ud558\ub294\ub370, \uc624\ub77c\ud074\uc5d0\ub294 ALL_TABLES, ALL_TAB_COLUMNS \uc640 \uac19\uc740 \uc2dc\uc2a4\ud15c \uce74\ud0c8\ub85c\uadf8\uac00 \uc788\uc5b4, \ud14c\uc774\ube14 \uc2a4\ud0a4\ub9c8 \uad00\ub828 \ud544\uc694 \uc815\ubcf4\ub97c \ucffc\ub9ac\ud574\ubcf4\uae30 \uc88b\uc558\ub294\ub370 \uc784\ud314\ub77c\uc5d0\ub294 \uc774\ub7f0 \uc2dc\uc2a4\ud15c \uce74\ud0c8\ub85c\uadf8\uac00 \uae30\ubcf8 \uc81c\uacf5\ub418\uace0 \uc788\uc9c0 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[161],"tags":[171,101,170,49,172],"class_list":["post-1494","post","type-post","status-publish","format-standard","hentry","category-impala","tag-all_tab_columns","tag-hive","tag-impala","tag-oracle","tag-system-catalog"],"_links":{"self":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts\/1494","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/comments?post=1494"}],"version-history":[{"count":0,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts\/1494\/revisions"}],"wp:attachment":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/media?parent=1494"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/categories?post=1494"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/tags?post=1494"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}