Consulta de AWS Glue Data Catalog

Debido a que muchos Servicios de AWS usan AWS Glue Data Catalog como repositorio central de metadatos, es posible que desee consultar metadatos del catálogo de datos. Para ello, puede utilizar consultas SQL en Athena. Puede utilizar Athena para consultar metadatos del catálogo de AWS Glue como bases de datos, tablas, particiones y columnas.

Para obtener metadatos del catálogo de AWS Glue, consulte la base de datos de information_schema en el backend de Athena. Las consultas de ejemplo de este tema muestran cómo utilizar Athena para consultar metadatos del catálogo de AWS Glue para casos de uso comunes.

Consideraciones y limitaciones

• En lugar de consultar la base de datos de information_schema, es posible utilizar los comandos DDL de Apache Hive individuales para extraer información de metadatos de bases de datos, tablas, vistas, particiones y columnas específicas de Athena. Pero la salida está en un formato no tabular.

• Hacer una consulta a information_schema es más eficaz si tiene una cantidad de pequeña a moderada demetadatos de AWS Glue. Si tiene una gran cantidad de metadatos, pueden producirse errores.

• No puede utilizar CREATE VIEW para crear una consulta en la base de datos information_schema.

Enumeración de bases de datos y búsqueda de una base de datos especificada

Los ejemplos de esta sección muestran cómo enumerar las bases de datos en metadatos por nombre de esquema.

ejemplo – Enumeración de las bases de datos

En la consulta de ejemplo siguiente se enumeran las bases de datos de la tabla information_schema.schemata.

SELECT schema_name
FROM   information_schema.schemata
LIMIT  10;

En la siguiente tabla se muestran los resultados de ejemplo.

6	alb-databas1
7	alb_original_cust
8	alblogsdatabase
9	athena_db_test
10	athena_ddl_db

ejemplo – Búsqueda de una base de datos especificada

En la siguiente consulta de ejemplo, rdspostgresql es una base de datos de ejemplo.

SELECT schema_name
FROM   information_schema.schemata
WHERE  schema_name = 'rdspostgresql'

En la siguiente tabla se muestran los resultados de ejemplo.

	schema_name
1	rdspostgresql

Enumeración de las tablas de una base de datos especificada y búsqueda de una tabla por nombre

Para enumerar los metadatos de las tablas, puede consultar por esquema de tabla o por nombre de tabla.

ejemplo – Enumeración de tablas por esquema

En la consulta siguiente se enumeran las tablas que utilizan el esquema de tabla rdspostgresql.

SELECT table_schema,
       table_name,
       table_type
FROM   information_schema.tables
WHERE  table_schema = 'rdspostgresql'

La siguiente imagen muestra un resultado de ejemplo.

	table_schema	table_name	table_type
1	rdspostgresql	rdspostgresqldb1_public_account	BASE TABLE

ejemplo – Búsqueda de una tabla por nombre

La siguiente consulta obtiene información de metadatos para la tabla athena1.

SELECT table_schema,
       table_name,
       table_type
FROM   information_schema.tables
WHERE  table_name = 'athena1'

La siguiente imagen muestra un resultado de ejemplo.

	table_schema	table_name	table_type
1	predeterminada	athena1	BASE TABLE

Enumeración de las particiones de una tabla específica

Puede utilizar SHOW PARTITIONS table_name para enumerar las particiones de una tabla especificada, como en el ejemplo siguiente.

SHOW PARTITIONS cloudtrail_logs_test2

También puede utilizar una consulta de metadatos de $partitions para enumerar los números de partición y los valores de partición de una tabla específica.

ejemplo – Consulta de las particiones de una tabla usando la sintaxis $partitions

En la consulta de ejemplo siguiente, se enumeran las particiones de la tabla cloudtrail_logs_test2 utilizando la sintaxis $partitions.

SELECT * FROM default."cloudtrail_logs_test2$partitions" ORDER BY partition_number

En la siguiente tabla se muestran los resultados de ejemplo.

	table_catalog	table_schema	table_name	Año	Mes	Día
1	awsdatacatalog	predeterminado	cloudtrail_logs_test2	2020	08	10
2	awsdatacatalog	predeterminado	cloudtrail_logs_test2	2020	08	11
3	awsdatacatalog	predeterminado	cloudtrail_logs_test2	2020	08	12

Enumeración de todas las columnas de todas las tablas

Puede enumerar todas las columnas de todas las tablas de AwsDataCatalog o de todas las tablas de una base de datos específica de AwsDataCatalog.

• Para mostrar todas las columnas de todas las bases de datos en AwsDataCatalog, use la consulta SELECT * FROM information_schema.columns.

• Para restringir los resultados a una base de datos específica, use table_schema='database_name' en la cláusula WHERE.

ejemplo – Enumeración de todas las tablas en una base de datos específica

En la consulta de ejemplo siguiente se enumeran todas las columnas de todas las tablas de la base de datos webdata.

SELECT * FROM information_schema.columns WHERE table_schema = 'webdata'            

Enumeración de las columnas que tienen en común tablas específicas

Puede enumerar las columnas que tienen en común tablas específicas de una base de datos.

• Utilice la sintaxis SELECT column_name FROM information_schema.columns.

• Para la cláusula WHERE, utilice la sintaxis WHERE table_name IN ('table1', 'table2').

ejemplo – Enumeración de columnas en común de dos tablas de la misma base de datos

El siguiente ejemplo de consulta enumera las columnas que tienen en común las tablas table1 y table2.

SELECT column_name
FROM information_schema.columns
WHERE table_name IN ('table1', 'table2')
GROUP BY column_name
HAVING COUNT(*) > 1;

Enumeración o búsqueda de las columnas de una tabla o una vista especificadas

Puede enumerar todas las columnas de una tabla, todas las columnas de una vista o buscar una columna por nombre en una base de datos y tabla especificados.

Para enumerar las columnas, utilice una consulta SELECT *. En la cláusula FROM, especifique information_schema.columns. En el cláusula WHERE, utilice table_schema='database_name' para especificar la base de datos y table_name = 'table_name' para especificar la tabla o vista que tiene las columnas que desea enumerar.

ejemplo – Enumeración de todas las columnas de una tabla especificada

En la consulta de ejemplo siguiente se enumeran todas las columnas de la tabla rdspostgresqldb1_public_account.

SELECT *
FROM   information_schema.columns
WHERE  table_schema = 'rdspostgresql'
       AND table_name = 'rdspostgresqldb1_public_account'

En la siguiente tabla se muestran los resultados de ejemplo.

	table_catalog	table_schema	table_name	column_name	ordinal_position	column_default	is_nullable	data_type	comentario	extra_info
1	awsdatacatalog	rdspostgresql	rdspostgresqldb1_public_account	password	1		SÍ	varchar
2	awsdatacatalog	rdspostgresql	rdspostgresqldb1_public_account	user_id	2		SÍ	integer
3	awsdatacatalog	rdspostgresql	rdspostgresqldb1_public_account	created_on	3		SÍ	Marca de tiempo
4	awsdatacatalog	rdspostgresql	rdspostgresqldb1_public_account	last_login	4		SÍ	Marca de tiempo
5	awsdatacatalog	rdspostgresql	rdspostgresqldb1_public_account	email	5		SÍ	varchar
6	awsdatacatalog	rdspostgresql	rdspostgresqldb1_public_account	username	6		SÍ	varchar

ejemplo – Enumeración de las columnas de una vista especificada

En la consulta de ejemplo siguiente se enumeran todas las columnas de la base de datos default para la vista arrayview.

SELECT *
FROM   information_schema.columns
WHERE  table_schema = 'default'
       AND table_name = 'arrayview'

En la siguiente tabla se muestran los resultados de ejemplo.

	table_catalog	table_schema	table_name	column_name	ordinal_position	column_default	is_nullable	data_type	comentario	extra_info
1	awsdatacatalog	predeterminado	arrayview	searchdate	1		SÍ	varchar
2	awsdatacatalog	predeterminado	arrayview	sid	2		SÍ	varchar
3	awsdatacatalog	predeterminado	arrayview	btid	3		SÍ	varchar
4	awsdatacatalog	predeterminado	arrayview	p	4		SÍ	varchar
5	awsdatacatalog	predeterminado	arrayview	infantprice	5		SÍ	varchar
6	awsdatacatalog	predeterminado	arrayview	sump	6		SÍ	varchar
7	awsdatacatalog	predeterminado	arrayview	journeymaparray	7		SÍ	array(varchar)

ejemplo – Búsqueda de una columna por nombre en una base de datos y una tabla especificadas

La consulta de ejemplo siguiente busca metadatos para la columna sid en la vista arrayview de la base de datos default.

SELECT *
FROM   information_schema.columns
WHERE  table_schema = 'default'
       AND table_name = 'arrayview' 
       AND column_name='sid'

La siguiente imagen muestra un resultado de ejemplo.

	table_catalog	table_schema	table_name	column_name	ordinal_position	column_default	is_nullable	data_type	comentario	extra_info
1	awsdatacatalog	predeterminado	arrayview	sid	2		SÍ	varchar