SHOW TABLE EXTENDED (Databricks SQL)

Muestra información de todas las tablas que coinciden con la expresión regular especificada. La salida incluye información básica de tabla e información del sistema de archivos como Last Access , , , , , , , , Created By , Type , y Provider Table Properties Location Serde Library InputFormat OutputFormat Storage Properties Partition Provider Partition Columns Schema .

Si hay una especificación de partición, genera la información específica del sistema de archivos de la partición especificada, como Partition Parameters y Partition Statistics . No se puede usar una expresión regular de tabla con una especificación de partición.

Sintaxis

SHOW TABLE EXTENDED [ { IN | FROM } database_name ] LIKE regex_pattern
    [ partition_spec ]

partition_spec
  PARTITION ( { partition_col_name1 = partition_col_val1 } [ , ... ] )

Parámetros

  • database_name

    Especifica el nombre de la base de datos. Si no se proporciona, usa la base de datos actual.

  • regex_pattern

    Patrón de expresión regular que se usa para filtrar las tablas no deseadas.

    • Excepto el * carácter y , el patrón funciona como una expresión | regular.
    • * solo coincide con 0 o más caracteres y se usa para separar | varias expresiones regulares diferentes, cualquiera de las cuales puede coincidir.
    • Los espacios en blanco iniciales y finales se recortan en el patrón de entrada antes del procesamiento. La coincidencia de patrones no tiene en cuenta mayúsculas de minúsculas.
  • partition_spec

    Lista separada por comas de pares clave-valor para particiones. No se puede usar una expresión regular de tabla con una especificación de partición.

Ejemplos

-- Assumes `employee` table partitioned by column `grade`
CREATE TABLE employee(name STRING, grade INT) PARTITIONED BY (grade);
INSERT INTO employee PARTITION (grade = 1) VALUES ('sam');
INSERT INTO employee PARTITION (grade = 2) VALUES ('suj');

-- Show the details of the table

SHOW TABLE EXTENDED LIKE 'employee';
+--------+---------+-----------+--------------------------------------------------------------+
|database|tableName|isTemporary|                         information                          |
+--------+---------+-----------+--------------------------------------------------------------+
|default |employee |false      |Database: default
                                Table: employee
                                Owner: root
                                Created Time: Fri Aug 30 15:10:21 IST 2019
                                Last Access: Thu Jan 01 05:30:00 IST 1970
                                Created By: Spark 3.0.0
                                Type: MANAGED
                                Provider: hive
                                Table Properties: [transient_lastDdlTime=1567158021]
                                Location: file:/opt/spark1/spark/spark-warehouse/employee
                                Serde Library: org.apache.hadoop.hive.serde2.lazy
                                .LazySimpleSerDe
                                InputFormat: org.apache.hadoop.mapred.TextInputFormat
                                OutputFormat: org.apache.hadoop.hive.ql.io
                                .HiveIgnoreKeyTextOutputFormat
                                Storage Properties: [serialization.format=1]
                                Partition Provider: Catalog
                                Partition Columns: [`grade`]
                                Schema: root
                                 |-- name: string (nullable = true)
                                 |-- grade: integer (nullable = true)
+--------+---------+-----------+--------------------------------------------------------------+
-- show multiple table details with pattern matching

SHOW TABLE EXTENDED  LIKE `employe*`;
+--------+---------+-----------+--------------------------------------------------------------+
|database|tableName|isTemporary|                         information                          |
+--------+---------+-----------+--------------------------------------------------------------+
|default |employee |false      |Database: default
                                Table: employee
                                Owner: root
                                Created Time: Fri Aug 30 15:10:21 IST 2019
                                Last Access: Thu Jan 01 05:30:00 IST 1970
                                Created By: Spark 3.0.0
                                Type: MANAGED
                                Provider: hive
                                Table Properties: [transient_lastDdlTime=1567158021]
                                Location: file:/opt/spark1/spark/spark-warehouse/employee
                                Serde Library: org.apache.hadoop.hive.serde2.lazy
                                .LazySimpleSerDe
                                InputFormat: org.apache.hadoop.mapred.TextInputFormat
                                OutputFormat: org.apache.hadoop.hive.ql.io
                                .HiveIgnoreKeyTextOutputFormat
                                Storage Properties: [serialization.format=1]
                                Partition Provider: Catalog
                                Partition Columns: [`grade`]
                                Schema: root
                                 |-- name: string (nullable = true)
                                 |-- grade: integer (nullable = true)

|default |employee1|false      |Database: default
                                Table: employee1
                                Owner: root
                                Created Time: Fri Aug 30 15:22:33 IST 2019
                                Last Access: Thu Jan 01 05:30:00 IST 1970
                                Created By: Spark 3.0.0
                                Type: MANAGED
                                Provider: hive
                                Table Properties: [transient_lastDdlTime=1567158753]
                                Location: file:/opt/spark1/spark/spark-warehouse/employee1
                                Serde Library: org.apache.hadoop.hive.serde2.lazy
                                .LazySimpleSerDe
                                InputFormat: org.apache.hadoop.mapred.TextInputFormat
                                OutputFormat: org.apache.hadoop.hive.ql.io
                                .HiveIgnoreKeyTextOutputFormat
                                Storage Properties: [serialization.format=1]
                                Partition Provider: Catalog
                                Schema: root
                                 |-- name: string (nullable = true)
+--------+---------+----------+---------------------------------------------------------------+
-- show partition file system details

SHOW TABLE EXTENDED  IN default LIKE `employee` PARTITION (`grade=1`);
+--------+---------+-----------+--------------------------------------------------------------+
|database|tableName|isTemporary|                         information                          |
+--------+---------+-----------+--------------------------------------------------------------+
|default |employee |false      |Partition Values: [grade=1]
                                Location: file:/opt/spark1/spark/spark-warehouse/employee
                                /grade=1
                                Serde Library: org.apache.hadoop.hive.serde2.lazy
                                .LazySimpleSerDe
                                InputFormat: org.apache.hadoop.mapred.TextInputFormat
                                OutputFormat: org.apache.hadoop.hive.ql.io
                                .HiveIgnoreKeyTextOutputFormat
                                Storage Properties: [serialization.format=1]
                                Partition Parameters: {rawDataSize=-1, numFiles=1,
                                transient_lastDdlTime=1567158221, totalSize=4,
                                COLUMN_STATS_ACCURATE=false, numRows=-1}
                                Created Time: Fri Aug 30 15:13:41 IST 2019
                                Last Access: Thu Jan 01 05:30:00 IST 1970
                                Partition Statistics: 4 bytes
+--------+---------+-----------+--------------------------------------------------------------+
-- show partition file system details with regex fail

SHOW TABLE EXTENDED  IN default LIKE `empl*` PARTITION (`grade=1`);
Error: Error running query:
 Table or view 'emplo*' not found in database 'default'; (state=,code=0)