CreateTableQueryGenerator implements QueryGenerator { @Override public Optional<String> getQuery(){ StringBuffer query = new StringBuffer(); query.append("CREATE TABLE "); query.append("`").append(tableMeta.getDatabase()).append("`").append("."); query.append("`").append(tableMeta.getTable()).append("`").append(" "); query.append("(").append(getColumnQuery(tableMeta.getColumns())).append(") "); if(null != tableMeta.getDetailedInfo() && null != tableMeta.getDetailedInfo().getParameters()){ String tableComment = tableMeta.getDetailedInfo().getParameters().get(COMMENT); if(!Strings.isNullOrEmpty(tableComment)){ tableComment = tableMeta.getDetailedInfo().getParameters().get(COMMENT.toLowerCase()); if(!Strings.isNullOrEmpty(tableComment)) { query.append(" COMMENT ").append(tableComment); } } } if(null != tableMeta.getPartitionInfo() ) { if (tableMeta.getPartitionInfo().getColumns() != null && !tableMeta.getPartitionInfo().getColumns().isEmpty()) { query.append(" PARTITIONED BY ( ").append(getColumnQuery(tableMeta.getPartitionInfo().getColumns())).append(")"); } } if(null != tableMeta.getStorageInfo()) { if (!QueryGenerationUtils.isNullOrEmpty(tableMeta.getStorageInfo().getBucketCols())) { query.append(" CLUSTERED BY (").append(Joiner.on(",").join(tableMeta.getStorageInfo().getBucketCols())).append(")"); } if (!QueryGenerationUtils.isNullOrEmpty(tableMeta.getStorageInfo().getSortCols())) { query.append(" SORTED BY (").append(getSortColQuery(tableMeta.getStorageInfo().getSortCols())).append(")"); } if (!Strings.isNullOrEmpty(tableMeta.getStorageInfo().getNumBuckets())) { query.append(" INTO ").append(tableMeta.getStorageInfo().getNumBuckets()).append(" BUCKETS "); } if(!isNullOrEmpty(tableMeta.getStorageInfo().getParameters())) { if (!Strings.isNullOrEmpty(tableMeta.getStorageInfo().getParameters().get(ESCAPE_DELIM)) || !Strings.isNullOrEmpty(tableMeta.getStorageInfo().getParameters().get(FIELD_DELIM)) || !Strings.isNullOrEmpty(tableMeta.getStorageInfo().getParameters().get(COLELCTION_DELIM)) || !Strings.isNullOrEmpty(tableMeta.getStorageInfo().getParameters().get(MAPKEY_DELIM)) || !Strings.isNullOrEmpty(tableMeta.getStorageInfo().getParameters().get(LINE_DELIM)) || !Strings.isNullOrEmpty(tableMeta.getStorageInfo().getParameters().get(SERIALIZATION_NULL_FORMAT)) ) { query.append(" ROW FORMAT DELIMITED "); if (!Strings.isNullOrEmpty(tableMeta.getStorageInfo().getParameters().get(FIELD_DELIM))) { query.append(" FIELDS TERMINATED BY '").append(tableMeta.getStorageInfo().getParameters().get(FIELD_DELIM)).append("'"); } if (!Strings.isNullOrEmpty(tableMeta.getStorageInfo().getParameters().get(ESCAPE_DELIM))) { query.append(" ESCAPED BY '").append(tableMeta.getStorageInfo().getParameters().get(ESCAPE_DELIM)).append("'"); } if (!Strings.isNullOrEmpty(tableMeta.getStorageInfo().getParameters().get(COLELCTION_DELIM))) { query.append(" COLLECTION ITEMS TERMINATED BY '").append(tableMeta.getStorageInfo().getParameters().get(COLELCTION_DELIM)).append("'"); } if (!Strings.isNullOrEmpty(tableMeta.getStorageInfo().getParameters().get(MAPKEY_DELIM))) { query.append(" MAP KEYS TERMINATED BY '").append(tableMeta.getStorageInfo().getParameters().get(MAPKEY_DELIM)).append("'"); } if (!Strings.isNullOrEmpty(tableMeta.getStorageInfo().getParameters().get(LINE_DELIM))) { query.append(" LINES TERMINATED BY '").append(tableMeta.getStorageInfo().getParameters().get(LINE_DELIM)).append("'"); } if (!Strings.isNullOrEmpty(tableMeta.getStorageInfo().getParameters().get(SERIALIZATION_NULL_FORMAT))) { query.append(" NULL DEFINED AS '").append(tableMeta.getStorageInfo().getParameters().get(SERIALIZATION_NULL_FORMAT)).append("'"); } } } if(!Strings.isNullOrEmpty(tableMeta.getStorageInfo().getFileFormat()) && !tableMeta.getStorageInfo().getFileFormat().trim().isEmpty()){ query.append(" STORED AS ").append(tableMeta.getStorageInfo().getFileFormat().trim()); }else if (!Strings.isNullOrEmpty(tableMeta.getStorageInfo().getInputFormat()) || !Strings.isNullOrEmpty(tableMeta.getStorageInfo().getOutputFormat()) ) { query.append(" STORED AS "); if (!Strings.isNullOrEmpty(tableMeta.getStorageInfo().getInputFormat())) { query.append(" INPUTFORMAT '").append(tableMeta.getStorageInfo().getInputFormat()).append("'"); } if (!Strings.isNullOrEmpty(tableMeta.getStorageInfo().getOutputFormat())) { query.append(" OUTPUTFORMAT '").append(tableMeta.getStorageInfo().getOutputFormat()).append("'"); } } } if(null != tableMeta.getDetailedInfo()) { if (!Strings.isNullOrEmpty(tableMeta.getDetailedInfo().getLocation())) { query.append(" LOCATION '").append(tableMeta.getDetailedInfo().getLocation()).append("'"); } if (QueryGenerationUtils.isNullOrEmpty(tableMeta.getDetailedInfo().getParameters())) { String props = QueryGenerationUtils.getPropertiesAsKeyValues(tableMeta.getDetailedInfo().getParameters()); query.append(" TBLPROPERTIES (").append(props).append(")"); } } return Optional.of(query.toString()); } CreateTableQueryGenerator(TableMeta tableMeta); @Override Optional<String> getQuery(); static final String ESCAPE_DELIM; static final String FIELD_DELIM; static final String COLELCTION_DELIM; static final String MAPKEY_DELIM; static final String LINE_DELIM; static final String SERIALIZATION_NULL_FORMAT; }
@Test public void testGetQuery() throws Exception { String createTableQuery = "CREATE TABLE `d1`.`t2` (`col_name1` string COMMENT 'col_name1 comment'," + "`col_name2` decimal(10,2) COMMENT 'col_name2 comment') PARTITIONED BY ( `col_name4` char(1) COMMENT 'col_name4 comment'," + "`col_name3` string COMMENT 'col_name3 comment') CLUSTERED BY (col_name1, col_name2) SORTED BY (col_name1 ASC,col_name2 DESC)" + " INTO 5 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ESCAPED BY '\\\\' STORED AS INPUTFORMAT " + "'org.apache.hadoop.mapred.SequenceFileInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat' " + "LOCATION 'hdfs: "'orc.compress'='SNAPPY','transient_lastDdlTime'='1481520077','NO_AUTO_COMPACTION'='true','comment'='table t1 comment'," + "'SORTBUCKETCOLSPREFIX'='TRUE')"; String json = "{\n" + "\t\"id\": \"d1/t2\",\n" + "\t\"database\": \"d1\",\n" + "\t\"table\": \"t2\",\n" + "\t\"columns\": [{\n" + "\t\t\"name\": \"col_name1\",\n" + "\t\t\"type\": \"string\",\n" + "\t\t\"comment\": \"col_name1 comment\"\n" + "\t}, {\n" + "\t\t\"name\": \"col_name2\",\n" + "\t\t\"type\": \"decimal(10,2)\",\n" + "\t\t\"comment\": \"col_name2 comment\"\n" + "\t}],\n" + "\t\"partitionInfo\": {\n" + "\t\t\"columns\": [{\n" + "\t\t\t\"name\": \"col_name4\",\n" + "\t\t\t\"type\": \"char(1)\",\n" + "\t\t\t\"comment\": \"col_name4 comment\"\n" + "\t\t}, {\n" + "\t\t\t\"name\": \"col_name3\",\n" + "\t\t\t\"type\": \"string\",\n" + "\t\t\t\"comment\": \"col_name3 comment\"\n" + "\t\t}]\n" + "\t},\n" + "\t\"detailedInfo\": {\n" + "\t\t\"dbName\": \"d1\",\n" + "\t\t\"owner\": \"admin\",\n" + "\t\t\"createTime\": \"Mon Dec 12 05:21:17 UTC 2016\",\n" + "\t\t\"lastAccessTime\": \"UNKNOWN\",\n" + "\t\t\"retention\": \"0\",\n" + "\t\t\"tableType\": \"MANAGED_TABLE\",\n" + "\t\t\"location\": \"hdfs: "\t\t\"parameters\": {\n" + "\t\t\t\"immutable\": \"false\",\n" + "\t\t\t\"orc.compress\": \"SNAPPY\",\n" + "\t\t\t\"transient_lastDdlTime\": \"1481520077\",\n" + "\t\t\t\"NO_AUTO_COMPACTION\": \"true\",\n" + "\t\t\t\"comment\": \"table t1 comment\",\n" + "\t\t\t\"SORTBUCKETCOLSPREFIX\": \"TRUE\"\n" + "\t\t}\n" + "\t},\n" + "\t\"storageInfo\": {\n" + "\t\t\"serdeLibrary\": \"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe\",\n" + "\t\t\"inputFormat\": \"org.apache.hadoop.mapred.SequenceFileInputFormat\",\n" + "\t\t\"outputFormat\": \"org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat\",\n" + "\t\t\"compressed\": \"No\",\n" + "\t\t\"numBuckets\": \"5\",\n" + "\t\t\"bucketCols\": [\"col_name1\", \" col_name2\"],\n" + "\t\t\"sortCols\": [{\n" + "\t\t\t\"columnName\": \"col_name1\",\n" + "\t\t\t\"order\": \"ASC\"\n" + "\t\t}, {\n" + "\t\t\t\"columnName\": \"col_name2\",\n" + "\t\t\t\"order\": \"DESC\"\n" + "\t\t}],\n" + "\t\t\"parameters\": {\n" + "\t\t\t\"escape.delim\": \"\\\\\\\\\",\n" + "\t\t\t\"field.delim\": \",\",\n" + "\t\t\t\"serialization.format\": \",\"\n" + "\t\t}\n" + "\t}\n" + "}"; TableMeta tableMeta = new Gson().fromJson(json, TableMeta.class); Optional<String> createQuery = new CreateTableQueryGenerator(tableMeta).getQuery(); LOG.info("createQuery : {}", createQuery); Assert.assertTrue(createQuery.isPresent()); Assert.assertEquals( "incorrect create table query.", createTableQuery, createQuery.get()); }