| ==== |
| ---- QUERY |
| # IMPALA-867: Test computing stats on Avro tables created by Hive with |
| # matching/mismatched column definitions and Avro schema. |
| # Clone the used tables here. |
| create table avro_hive_alltypes |
| like functional_avro_snap.alltypes; |
| create table avro_hive_alltypes_extra_coldef |
| like functional_avro_snap.alltypes_extra_coldef; |
| create table avro_hive_alltypes_missing_coldef |
| like functional_avro_snap.alltypes_missing_coldef; |
| create table avro_hive_alltypes_type_mismatch |
| like functional_avro_snap.alltypes_type_mismatch; |
| create table avro_hive_no_avro_schema |
| like functional_avro_snap.no_avro_schema; |
| ==== |
| ---- QUERY |
| # Avro table with matching column definitions and Avro schema |
| compute stats avro_hive_alltypes |
| ---- RESULTS |
| 'Updated 0 partition(s) and 11 column(s).' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| show table stats avro_hive_alltypes |
| ---- LABELS |
| YEAR, MONTH, #ROWS, #FILES, SIZE, BYTES CACHED, CACHE REPLICATION, FORMAT, INCREMENTAL STATS, LOCATION |
| ---- RESULTS |
| 'Total','',0,0,'0B','0B','','','','' |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| show column stats avro_hive_alltypes |
| ---- LABELS |
| COLUMN, TYPE, #DISTINCT VALUES, #NULLS, MAX SIZE, AVG SIZE |
| ---- RESULTS |
| 'id','INT',0,0,4,4 |
| 'bool_col','BOOLEAN',2,0,1,1 |
| 'tinyint_col','INT',0,0,4,4 |
| 'smallint_col','INT',0,0,4,4 |
| 'int_col','INT',0,0,4,4 |
| 'bigint_col','BIGINT',0,0,8,8 |
| 'float_col','FLOAT',0,0,4,4 |
| 'double_col','DOUBLE',0,0,8,8 |
| 'date_string_col','STRING',0,0,0,0 |
| 'string_col','STRING',0,0,0,0 |
| 'timestamp_col','STRING',0,0,0,0 |
| 'year','INT',0,0,4,4 |
| 'month','INT',0,0,4,4 |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, BIGINT, DOUBLE |
| ==== |
| ---- QUERY |
| # Avro table with an extra column definition. |
| compute stats avro_hive_alltypes_extra_coldef |
| ---- CATCH |
| AnalysisException: Cannot COMPUTE STATS on Avro table 'avro_hive_alltypes_extra_coldef' because its column definitions do not match those in the Avro schema. |
| Missing Avro-schema column corresponding to column definition 'extra_col' of type 'string' at position '10'. |
| Please re-create the table with column definitions, e.g., using the result of 'SHOW CREATE TABLE' |
| ==== |
| ---- QUERY |
| show table stats avro_hive_alltypes_extra_coldef |
| ---- LABELS |
| YEAR, MONTH, #ROWS, #FILES, SIZE, BYTES CACHED, CACHE REPLICATION, FORMAT, INCREMENTAL STATS, LOCATION |
| ---- RESULTS |
| 'Total','',-1,0,'0B','0B','','','','' |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| show column stats avro_hive_alltypes_extra_coldef |
| ---- LABELS |
| COLUMN, TYPE, #DISTINCT VALUES, #NULLS, MAX SIZE, AVG SIZE |
| ---- RESULTS |
| 'id','INT',-1,-1,4,4 |
| 'bool_col','BOOLEAN',-1,-1,1,1 |
| 'tinyint_col','INT',-1,-1,4,4 |
| 'smallint_col','INT',-1,-1,4,4 |
| 'int_col','INT',-1,-1,4,4 |
| 'bigint_col','BIGINT',-1,-1,8,8 |
| 'float_col','FLOAT',-1,-1,4,4 |
| 'double_col','DOUBLE',-1,-1,8,8 |
| 'date_string_col','STRING',-1,-1,-1,-1 |
| 'string_col','STRING',-1,-1,-1,-1 |
| 'timestamp_col','STRING',-1,-1,-1,-1 |
| 'year','INT',0,0,4,4 |
| 'month','INT',0,0,4,4 |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, BIGINT, DOUBLE |
| ==== |
| ---- QUERY |
| # Avro table with missing two column definitions. |
| compute stats avro_hive_alltypes_missing_coldef |
| ---- CATCH |
| MESSAGE: AnalysisException: Cannot COMPUTE STATS on Avro table 'avro_hive_alltypes_missing_coldef' because its column definitions do not match those in the Avro schema. |
| Definition of column 'smallint_col' of type 'smallint' does not match the Avro-schema column 'tinyint_col' of type 'INT' at position '2'. |
| Please re-create the table with column definitions, e.g., using the result of 'SHOW CREATE TABLE' |
| ==== |
| ---- QUERY |
| show table stats avro_hive_alltypes_missing_coldef |
| ---- LABELS |
| YEAR, MONTH, #ROWS, #FILES, SIZE, BYTES CACHED, CACHE REPLICATION, FORMAT, INCREMENTAL STATS, LOCATION |
| ---- RESULTS |
| 'Total','',-1,0,'0B','0B','','','','' |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| show column stats avro_hive_alltypes_missing_coldef |
| ---- LABELS |
| COLUMN, TYPE, #DISTINCT VALUES, #NULLS, MAX SIZE, AVG SIZE |
| ---- RESULTS |
| 'bigint_col','BIGINT',-1,-1,8,8 |
| 'bool_col','BOOLEAN',-1,-1,1,1 |
| 'date_string_col','STRING',-1,-1,-1,-1 |
| 'double_col','DOUBLE',-1,-1,8,8 |
| 'float_col','FLOAT',-1,-1,4,4 |
| 'id','INT',-1,-1,4,4 |
| 'int_col','INT',-1,-1,4,4 |
| 'month','INT',0,0,4,4 |
| 'smallint_col','INT',-1,-1,4,4 |
| 'string_col','STRING',-1,-1,-1,-1 |
| 'timestamp_col','STRING',-1,-1,-1,-1 |
| 'tinyint_col','INT',-1,-1,4,4 |
| 'year','INT',0,0,4,4 |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, BIGINT, DOUBLE |
| ==== |
| ---- QUERY |
| # Avro table with one column definition having a different |
| # type than the Avro schema (bigint_col is a string). |
| compute stats avro_hive_alltypes_type_mismatch |
| ---- RESULTS |
| 'Updated 0 partition(s) and 11 column(s).' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| show table stats avro_hive_alltypes_type_mismatch |
| ---- LABELS |
| YEAR, MONTH, #ROWS, #FILES, SIZE, BYTES CACHED, CACHE REPLICATION, FORMAT, INCREMENTAL STATS, LOCATION |
| ---- RESULTS |
| 'Total','',0,0,'0B','0B','','','','' |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| show column stats avro_hive_alltypes_type_mismatch |
| ---- LABELS |
| COLUMN, TYPE, #DISTINCT VALUES, #NULLS, MAX SIZE, AVG SIZE |
| ---- RESULTS |
| 'id','INT',0,0,4,4 |
| 'bool_col','BOOLEAN',2,0,1,1 |
| 'tinyint_col','INT',0,0,4,4 |
| 'smallint_col','INT',0,0,4,4 |
| 'int_col','INT',0,0,4,4 |
| 'bigint_col','BIGINT',-1,-1,8,8 |
| 'float_col','FLOAT',0,0,4,4 |
| 'double_col','DOUBLE',0,0,8,8 |
| 'date_string_col','STRING',0,0,0,0 |
| 'string_col','STRING',0,0,0,0 |
| 'timestamp_col','STRING',-1,-1,-1,-1 |
| 'year','INT',0,0,4,4 |
| 'month','INT',0,0,4,4 |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, BIGINT, DOUBLE |
| ==== |
| ---- QUERY |
| # Avro table without an Avro schema created by Hive. |
| # The Avro schema is inferred from the column definitions, |
| compute stats avro_hive_no_avro_schema |
| ---- RESULTS |
| 'Updated 0 partition(s) and 11 column(s).' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| show table stats avro_hive_no_avro_schema |
| ---- LABELS |
| YEAR, MONTH, #ROWS, #FILES, SIZE, BYTES CACHED, CACHE REPLICATION, FORMAT, INCREMENTAL STATS, LOCATION |
| ---- RESULTS |
| 'Total','',0,0,'0B','0B','','','','' |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| show column stats avro_hive_no_avro_schema |
| ---- LABELS |
| COLUMN, TYPE, #DISTINCT VALUES, #NULLS, MAX SIZE, AVG SIZE |
| ---- RESULTS |
| 'id','INT',0,0,4,4 |
| 'bool_col','BOOLEAN',2,0,1,1 |
| 'tinyint_col','INT',0,0,4,4 |
| 'smallint_col','INT',0,0,4,4 |
| 'int_col','INT',0,0,4,4 |
| 'bigint_col','BIGINT',0,0,8,8 |
| 'float_col','FLOAT',0,0,4,4 |
| 'double_col','DOUBLE',0,0,8,8 |
| 'date_string_col','STRING',0,0,0,0 |
| 'string_col','STRING',0,0,0,0 |
| 'timestamp_col','STRING',0,0,0,0 |
| 'year','INT',0,0,4,4 |
| 'month','INT',0,0,4,4 |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, BIGINT, DOUBLE |
| ==== |
| ---- QUERY |
| # Test Avro table created without any column definitions. |
| create table avro_impala_alltypes_no_coldefs |
| partitioned by (year int, month int) |
| with serdeproperties |
| ('avro.schema.url'='$FILESYSTEM_PREFIX/test-warehouse/avro_schemas/functional/alltypes.json') |
| stored as avro; |
| ==== |
| ---- QUERY |
| compute stats avro_impala_alltypes_no_coldefs |
| ---- RESULTS |
| 'Updated 0 partition(s) and 11 column(s).' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| show table stats avro_impala_alltypes_no_coldefs |
| ---- LABELS |
| YEAR, MONTH, #ROWS, #FILES, SIZE, BYTES CACHED, CACHE REPLICATION, FORMAT, INCREMENTAL STATS, LOCATION |
| ---- RESULTS |
| 'Total','',0,0,'0B','0B','','','','' |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| show column stats avro_impala_alltypes_no_coldefs |
| ---- LABELS |
| COLUMN, TYPE, #DISTINCT VALUES, #NULLS, MAX SIZE, AVG SIZE |
| ---- RESULTS |
| 'id','INT',0,0,4,4 |
| 'bool_col','BOOLEAN',2,0,1,1 |
| 'tinyint_col','INT',0,0,4,4 |
| 'smallint_col','INT',0,0,4,4 |
| 'int_col','INT',0,0,4,4 |
| 'bigint_col','BIGINT',0,0,8,8 |
| 'float_col','FLOAT',0,0,4,4 |
| 'double_col','DOUBLE',0,0,8,8 |
| 'date_string_col','STRING',0,0,0,0 |
| 'string_col','STRING',0,0,0,0 |
| 'timestamp_col','STRING',0,0,0,0 |
| 'year','INT',0,0,4,4 |
| 'month','INT',0,0,4,4 |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, BIGINT, DOUBLE |
| ==== |
| ---- QUERY |
| # IMPALA-1104: Test computing stats on Avro tables created by Impala |
| # with mismatched column definitions and Avro schema. Mismatched column name. |
| create table avro_impala_alltypes_bad_colname |
| (id int, bool_col boolean, tinyint_col int, smallint_col int, bad_int_col int, |
| bigint_col bigint, float_col float, double_col double, date_string_col string, |
| string_col string, timestamp_col timestamp) |
| partitioned by (year int, month int) |
| with serdeproperties |
| ('avro.schema.url'='$FILESYSTEM_PREFIX/test-warehouse/avro_schemas/functional/alltypes.json') |
| stored as avro; |
| ==== |
| ---- QUERY |
| compute stats avro_impala_alltypes_bad_colname |
| ---- RESULTS |
| 'Updated 0 partition(s) and 11 column(s).' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| show table stats avro_impala_alltypes_bad_colname |
| ---- LABELS |
| YEAR, MONTH, #ROWS, #FILES, SIZE, BYTES CACHED, CACHE REPLICATION, FORMAT, INCREMENTAL STATS, LOCATION |
| ---- RESULTS |
| 'Total','',0,0,'0B','0B','','','','' |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| show column stats avro_impala_alltypes_bad_colname |
| ---- LABELS |
| COLUMN, TYPE, #DISTINCT VALUES, #NULLS, MAX SIZE, AVG SIZE |
| ---- RESULTS |
| 'id','INT',0,0,4,4 |
| 'bool_col','BOOLEAN',2,0,1,1 |
| 'tinyint_col','INT',0,0,4,4 |
| 'smallint_col','INT',0,0,4,4 |
| 'int_col','INT',0,0,4,4 |
| 'bigint_col','BIGINT',0,0,8,8 |
| 'float_col','FLOAT',0,0,4,4 |
| 'double_col','DOUBLE',0,0,8,8 |
| 'date_string_col','STRING',0,0,0,0 |
| 'string_col','STRING',0,0,0,0 |
| 'timestamp_col','STRING',0,0,0,0 |
| 'year','INT',0,0,4,4 |
| 'month','INT',0,0,4,4 |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, BIGINT, DOUBLE |
| ==== |
| ---- QUERY |
| # IMPALA-1104: Test computing stats on Avro tables created by Impala |
| # with mismatched column definitions and Avro schema. Mismatched column type. |
| create table avro_impala_alltypes_bad_coltype |
| (id int, bool_col boolean, tinyint_col int, smallint_col int, int_col int, |
| bigint_col bigint, float_col float, double_col bigint, date_string_col string, |
| string_col string, timestamp_col timestamp) |
| partitioned by (year int, month int) |
| with serdeproperties |
| ('avro.schema.url'='$FILESYSTEM_PREFIX/test-warehouse/avro_schemas/functional/alltypes.json') |
| stored as avro; |
| ==== |
| ---- QUERY |
| compute stats avro_impala_alltypes_bad_coltype |
| ---- RESULTS |
| 'Updated 0 partition(s) and 11 column(s).' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| show table stats avro_impala_alltypes_bad_coltype |
| ---- LABELS |
| YEAR, MONTH, #ROWS, #FILES, SIZE, BYTES CACHED, CACHE REPLICATION, FORMAT, INCREMENTAL STATS, LOCATION |
| ---- RESULTS |
| 'Total','',0,0,'0B','0B','','','','' |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| show column stats avro_impala_alltypes_bad_coltype |
| ---- LABELS |
| COLUMN, TYPE, #DISTINCT VALUES, #NULLS, MAX SIZE, AVG SIZE |
| ---- RESULTS |
| 'id','INT',0,0,4,4 |
| 'bool_col','BOOLEAN',2,0,1,1 |
| 'tinyint_col','INT',0,0,4,4 |
| 'smallint_col','INT',0,0,4,4 |
| 'int_col','INT',0,0,4,4 |
| 'bigint_col','BIGINT',0,0,8,8 |
| 'float_col','FLOAT',0,0,4,4 |
| 'double_col','DOUBLE',0,0,8,8 |
| 'date_string_col','STRING',0,0,0,0 |
| 'string_col','STRING',0,0,0,0 |
| 'timestamp_col','STRING',0,0,0,0 |
| 'year','INT',0,0,4,4 |
| 'month','INT',0,0,4,4 |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, BIGINT, DOUBLE |
| ==== |
| ---- QUERY |
| # Test that compute stats on a Hive-created Avro table without column defs |
| # works (HIVE-6308, IMPALA-867). |
| create table alltypes_no_coldef like functional_avro_snap.alltypes_no_coldef; |
| compute stats alltypes_no_coldef |
| ---- RESULTS |
| 'Updated 1 partition(s) and 11 column(s).' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| show column stats alltypes_no_coldef |
| ---- RESULTS |
| 'id','INT',0,0,4,4 |
| 'bool_col','BOOLEAN',2,0,1,1 |
| 'tinyint_col','INT',0,0,4,4 |
| 'smallint_col','INT',0,0,4,4 |
| 'int_col','INT',0,0,4,4 |
| 'bigint_col','BIGINT',0,0,8,8 |
| 'float_col','FLOAT',0,0,4,4 |
| 'double_col','DOUBLE',0,0,8,8 |
| 'date_string_col','STRING',0,0,0,0 |
| 'string_col','STRING',0,0,0,0 |
| 'timestamp_col','STRING',0,0,0,0 |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, BIGINT, DOUBLE |
| ==== |
