Details

    • Type: New Feature New Feature
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.10.0
    • Component/s: Metastore, Query Processor
    • Labels:
      None

      Description

      SHOW CREATE TABLE would be very useful in cases where you are trying to figure out the partitioning and/or bucketing scheme for a table. Perhaps this could be implemented by having new tables automatically SET PROPERTIES (create_command='raw text of the create statement')?

      1. show_create.txt
        13 kB
        Edward Capriolo
      2. HiveShowCreateTable.jar
        7 kB
        Jay Ramadorai
      3. HIVE-967.patch.txt
        34 kB
        Feng Lu
      4. HIVE-967.6.patch.txt
        50 kB
        Feng Lu
      5. HIVE-967.5.patch.txt
        50 kB
        Feng Lu
      6. HIVE-967.4.patch.txt
        49 kB
        Feng Lu
      7. HIVE-967.3.patch.txt
        49 kB
        Feng Lu
      8. HIVE-967.2.patch.txt
        43 kB
        Feng Lu

        Issue Links

          Activity

          Hide
          Namit Jain added a comment -

          It should also contain all DDL changes after the table is created. I mean, if a alter was performed on the table, the CREATE TABLE should
          reflect that

          Show
          Namit Jain added a comment - It should also contain all DDL changes after the table is created. I mean, if a alter was performed on the table, the CREATE TABLE should reflect that
          Hide
          Edward Capriolo added a comment -

          @Carl. Are you still working on this. I have a working show create table that talks to the metastore but have not created and DML for it yet.

          Show
          Edward Capriolo added a comment - @Carl. Are you still working on this. I have a working show create table that talks to the metastore but have not created and DML for it yet.
          Hide
          Carl Steinbach added a comment -

          @Ed: Sounds good, reassigning this to you.

          Show
          Carl Steinbach added a comment - @Ed: Sounds good, reassigning this to you.
          Hide
          Edward Capriolo added a comment -

          Not a patch on hive-trunk. but a program that can read the metastore and generate some descent show creates

          Show
          Edward Capriolo added a comment - Not a patch on hive-trunk. but a program that can read the metastore and generate some descent show creates
          Hide
          Jay Ramadorai added a comment -

          Jar of Ed's Show Create Table utility

          Show
          Jay Ramadorai added a comment - Jar of Ed's Show Create Table utility
          Hide
          Feng Lu added a comment -

          Implemented Show CreateTable query.

          Show
          Feng Lu added a comment - Implemented Show CreateTable query.
          Hide
          Feng Lu added a comment -

          Review is available at https://reviews.facebook.net/D4161 .

          Show
          Feng Lu added a comment - Review is available at https://reviews.facebook.net/D4161 .
          Hide
          Carl Steinbach added a comment -

          @Feng: I left some review comments on phabricator. Thanks.

          Show
          Carl Steinbach added a comment - @Feng: I left some review comments on phabricator. Thanks.
          Hide
          Namit Jain added a comment -

          @Feng, some more comments from me

          Show
          Namit Jain added a comment - @Feng, some more comments from me
          Hide
          Feng Lu added a comment -

          Uploading the patch for the current differential.

          Show
          Feng Lu added a comment - Uploading the patch for the current differential.
          Hide
          Feng Lu added a comment -

          The diff is updated and a current patch is attached here.

          Show
          Feng Lu added a comment - The diff is updated and a current patch is attached here.
          Hide
          Kevin Wilfong added a comment -

          Made a couple comments on the diff.

          Show
          Kevin Wilfong added a comment - Made a couple comments on the diff.
          Hide
          Feng Lu added a comment -

          A new patch is uploaded

          Show
          Feng Lu added a comment - A new patch is uploaded
          Hide
          Kevin Wilfong added a comment -

          +1

          Show
          Kevin Wilfong added a comment - +1
          Hide
          Kevin Wilfong added a comment -

          patch doesn't apply cleanly, could you update?

          Show
          Kevin Wilfong added a comment - patch doesn't apply cleanly, could you update?
          Hide
          Feng Lu added a comment -

          @Kevin, I did an update and some other code change. Please see the review page for details.

          Show
          Feng Lu added a comment - @Kevin, I did an update and some other code change. Please see the review page for details.
          Hide
          Kevin Wilfong added a comment -

          Thanks for updating, running tests.

          Show
          Kevin Wilfong added a comment - Thanks for updating, running tests.
          Hide
          Steven Wong added a comment -

          I'm having trouble with Fabricator login, so here's my comment on a piece of the code:

          String createTab_stmt = "CREATE VIEW " + tableName + " AS " + tbl.getViewOriginalText();

          Shouldn't getViewExpandedText be used instead? Because views are supposed to be "frozen at the time the view is created."

          Show
          Steven Wong added a comment - I'm having trouble with Fabricator login, so here's my comment on a piece of the code: String createTab_stmt = "CREATE VIEW " + tableName + " AS " + tbl.getViewOriginalText(); Shouldn't getViewExpandedText be used instead? Because views are supposed to be "frozen at the time the view is created."
          Hide
          Kevin Wilfong added a comment -

          I don't know what you mean by "frozen at the time the view is created". A view is just an alias for a subquery, and in both cases the query text is stored in the metastore, and hence frozen.

          Show
          Kevin Wilfong added a comment - I don't know what you mean by "frozen at the time the view is created". A view is just an alias for a subquery, and in both cases the query text is stored in the metastore, and hence frozen.
          Hide
          Steven Wong added a comment -

          @Kevin, https://cwiki.apache.org/confluence/display/Hive/ViewDev#ViewDev-StoredViewDefinition explains "frozen at the time the view is created" better than I can.

          Here is the scenario I have in mind:

          1. Create a view v as select * from t, where t is a table with columns a and b. Hence, select * from v will return a and b.
          2. Add a new column c to t. Per the view dev wiki page above, select * from v will still return just a and b.
          3. Do show create table on v and save the output.
          4. Drop v. (Maybe this is done intentionally, accidentally, or maliciously by someone else.)
          5. Run the saved output to re-create v.
          6. Now, should select * from v return just a and b, or should it return a, b, and c? This is basically the issue I'm trying to point out. I think "a and b" looks like the better answer, which will mean using getViewExpandedText in show create table.
          Show
          Steven Wong added a comment - @Kevin, https://cwiki.apache.org/confluence/display/Hive/ViewDev#ViewDev-StoredViewDefinition explains "frozen at the time the view is created" better than I can. Here is the scenario I have in mind: Create a view v as select * from t, where t is a table with columns a and b. Hence, select * from v will return a and b. Add a new column c to t. Per the view dev wiki page above, select * from v will still return just a and b. Do show create table on v and save the output. Drop v. (Maybe this is done intentionally, accidentally, or maliciously by someone else.) Run the saved output to re-create v. Now, should select * from v return just a and b, or should it return a, b, and c? This is basically the issue I'm trying to point out. I think "a and b" looks like the better answer, which will mean using getViewExpandedText in show create table.
          Hide
          Kevin Wilfong added a comment -

          Thanks Steven, I understand now. You're right.

          Feng if you're still working on this can you update it as Steven suggested.

          Show
          Kevin Wilfong added a comment - Thanks Steven, I understand now. You're right. Feng if you're still working on this can you update it as Steven suggested.
          Hide
          Feng Lu added a comment -

          @Kevin, I don't have access to a linux machine these few days, but I can update this later.

          Show
          Feng Lu added a comment - @Kevin, I don't have access to a linux machine these few days, but I can update this later.
          Hide
          Kevin Wilfong added a comment -

          +1

          Show
          Kevin Wilfong added a comment - +1
          Hide
          Kevin Wilfong added a comment -

          Committed, thanks Feng.

          Show
          Kevin Wilfong added a comment - Committed, thanks Feng.
          Hide
          Hudson added a comment -

          Integrated in Hive-trunk-h0.21 #1742 (See https://builds.apache.org/job/Hive-trunk-h0.21/1742/)
          HIVE-967. Implement "show create table" (Feng Lu via kevinwilfong) (Revision 1398896)

          Result = FAILURE
          kevinwilfong : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1398896
          Files :

          • /hive/trunk/build.xml
          • /hive/trunk/eclipse-templates/.classpath
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/ErrorMsg.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/DDLTask.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/DDLSemanticAnalyzer.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/Hive.g
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzerFactory.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/DDLWork.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/HiveOperation.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/ShowCreateTableDesc.java
          • /hive/trunk/ql/src/test/queries/clientnegative/show_create_table_does_not_exist.q
          • /hive/trunk/ql/src/test/queries/clientnegative/show_create_table_index.q
          • /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_alter.q
          • /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_db_table.q
          • /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_delimited.q
          • /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_partitioned.q
          • /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_serde.q
          • /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_view.q
          • /hive/trunk/ql/src/test/results/clientnegative/show_create_table_does_not_exist.q.out
          • /hive/trunk/ql/src/test/results/clientnegative/show_create_table_index.q.out
          • /hive/trunk/ql/src/test/results/clientpositive/show_create_table_alter.q.out
          • /hive/trunk/ql/src/test/results/clientpositive/show_create_table_db_table.q.out
          • /hive/trunk/ql/src/test/results/clientpositive/show_create_table_delimited.q.out
          • /hive/trunk/ql/src/test/results/clientpositive/show_create_table_partitioned.q.out
          • /hive/trunk/ql/src/test/results/clientpositive/show_create_table_serde.q.out
          • /hive/trunk/ql/src/test/results/clientpositive/show_create_table_view.q.out
          Show
          Hudson added a comment - Integrated in Hive-trunk-h0.21 #1742 (See https://builds.apache.org/job/Hive-trunk-h0.21/1742/ ) HIVE-967 . Implement "show create table" (Feng Lu via kevinwilfong) (Revision 1398896) Result = FAILURE kevinwilfong : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1398896 Files : /hive/trunk/build.xml /hive/trunk/eclipse-templates/.classpath /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/ErrorMsg.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/DDLTask.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/DDLSemanticAnalyzer.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/Hive.g /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzerFactory.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/DDLWork.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/HiveOperation.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/ShowCreateTableDesc.java /hive/trunk/ql/src/test/queries/clientnegative/show_create_table_does_not_exist.q /hive/trunk/ql/src/test/queries/clientnegative/show_create_table_index.q /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_alter.q /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_db_table.q /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_delimited.q /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_partitioned.q /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_serde.q /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_view.q /hive/trunk/ql/src/test/results/clientnegative/show_create_table_does_not_exist.q.out /hive/trunk/ql/src/test/results/clientnegative/show_create_table_index.q.out /hive/trunk/ql/src/test/results/clientpositive/show_create_table_alter.q.out /hive/trunk/ql/src/test/results/clientpositive/show_create_table_db_table.q.out /hive/trunk/ql/src/test/results/clientpositive/show_create_table_delimited.q.out /hive/trunk/ql/src/test/results/clientpositive/show_create_table_partitioned.q.out /hive/trunk/ql/src/test/results/clientpositive/show_create_table_serde.q.out /hive/trunk/ql/src/test/results/clientpositive/show_create_table_view.q.out
          Hide
          Hudson added a comment -

          Integrated in Hive-trunk-hadoop2 #54 (See https://builds.apache.org/job/Hive-trunk-hadoop2/54/)
          HIVE-967. Implement "show create table" (Feng Lu via kevinwilfong) (Revision 1398896)

          Result = ABORTED
          kevinwilfong : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1398896
          Files :

          • /hive/trunk/build.xml
          • /hive/trunk/eclipse-templates/.classpath
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/ErrorMsg.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/DDLTask.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/DDLSemanticAnalyzer.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/Hive.g
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzerFactory.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/DDLWork.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/HiveOperation.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/ShowCreateTableDesc.java
          • /hive/trunk/ql/src/test/queries/clientnegative/show_create_table_does_not_exist.q
          • /hive/trunk/ql/src/test/queries/clientnegative/show_create_table_index.q
          • /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_alter.q
          • /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_db_table.q
          • /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_delimited.q
          • /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_partitioned.q
          • /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_serde.q
          • /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_view.q
          • /hive/trunk/ql/src/test/results/clientnegative/show_create_table_does_not_exist.q.out
          • /hive/trunk/ql/src/test/results/clientnegative/show_create_table_index.q.out
          • /hive/trunk/ql/src/test/results/clientpositive/show_create_table_alter.q.out
          • /hive/trunk/ql/src/test/results/clientpositive/show_create_table_db_table.q.out
          • /hive/trunk/ql/src/test/results/clientpositive/show_create_table_delimited.q.out
          • /hive/trunk/ql/src/test/results/clientpositive/show_create_table_partitioned.q.out
          • /hive/trunk/ql/src/test/results/clientpositive/show_create_table_serde.q.out
          • /hive/trunk/ql/src/test/results/clientpositive/show_create_table_view.q.out
          Show
          Hudson added a comment - Integrated in Hive-trunk-hadoop2 #54 (See https://builds.apache.org/job/Hive-trunk-hadoop2/54/ ) HIVE-967 . Implement "show create table" (Feng Lu via kevinwilfong) (Revision 1398896) Result = ABORTED kevinwilfong : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1398896 Files : /hive/trunk/build.xml /hive/trunk/eclipse-templates/.classpath /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/ErrorMsg.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/DDLTask.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/DDLSemanticAnalyzer.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/Hive.g /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzerFactory.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/DDLWork.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/HiveOperation.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/ShowCreateTableDesc.java /hive/trunk/ql/src/test/queries/clientnegative/show_create_table_does_not_exist.q /hive/trunk/ql/src/test/queries/clientnegative/show_create_table_index.q /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_alter.q /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_db_table.q /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_delimited.q /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_partitioned.q /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_serde.q /hive/trunk/ql/src/test/queries/clientpositive/show_create_table_view.q /hive/trunk/ql/src/test/results/clientnegative/show_create_table_does_not_exist.q.out /hive/trunk/ql/src/test/results/clientnegative/show_create_table_index.q.out /hive/trunk/ql/src/test/results/clientpositive/show_create_table_alter.q.out /hive/trunk/ql/src/test/results/clientpositive/show_create_table_db_table.q.out /hive/trunk/ql/src/test/results/clientpositive/show_create_table_delimited.q.out /hive/trunk/ql/src/test/results/clientpositive/show_create_table_partitioned.q.out /hive/trunk/ql/src/test/results/clientpositive/show_create_table_serde.q.out /hive/trunk/ql/src/test/results/clientpositive/show_create_table_view.q.out
          Hide
          Ashutosh Chauhan added a comment -

          This issue is fixed and released as part of 0.10.0 release. If you find an issue which seems to be related to this one, please create a new jira and link this one with new jira.

          Show
          Ashutosh Chauhan added a comment - This issue is fixed and released as part of 0.10.0 release. If you find an issue which seems to be related to this one, please create a new jira and link this one with new jira.

            People

            • Assignee:
              Feng Lu
              Reporter:
              Adam Kramer
            • Votes:
              3 Vote for this issue
              Watchers:
              16 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development