AWS Glue Data Catalog 쿼리

AWS Glue Data Catalog는 많은 AWS 서비스에서 중앙 메타데이터 리포지토리로 사용되므로 데이터 카탈로그 메타데이터를 쿼리해야 할 수 있습니다. 이를 위해 Athena에서 SQL 쿼리를 사용할 수 있습니다. Athena를 사용하여 데이터베이스, 테이블, 파티션 및 열과 같은 AWS Glue 카탈로그 메타데이터를 쿼리할 수 있습니다.

AWS Glue 카탈로그 메타데이터를 가져오려면 Athena 백엔드에서 information_schema 데이터베이스를 쿼리합니다. 이 주제의 예제 쿼리에서는 Athena를 사용하여 일반적인 사용 사례에 대한 AWS Glue 카탈로그 메타데이터를 쿼리하는 방법을 보여줍니다.

고려 사항 및 제한

• information_schema 데이터베이스를 쿼리하는 대신, 개별 Apache Hive DDL 명령를 사용하여 Athena에서 특정 데이터베이스, 테이블, 뷰, 파티션 및 열에 대한 메타데이터 정보를 추출할 수 있습니다. 그러나 출력은 표가 아닌 형식을 가집니다.

• AWS Glue 메타데이터의 양이 소량에서 중간 정도라면 information_schema 쿼리가 가장 성능이 뛰어납니다. 메타데이터의 양이 많으면 오류가 발생할 수 있습니다.

• CREATE VIEW를 사용하여 information_schema 데이터베이스에 뷰를 생성할 수 없습니다.

데이터베이스 나열 및 지정된 데이터베이스 검색

이 단원의 예제는 스키마 이름별로 메타데이터에서 데이터베이스를 나열하는 방법을 보여줍니다.

예 - 데이터베이스 나열

다음 예제 쿼리는 information_schema.schemata 테이블의 데이터베이스를 나열합니다.

SELECT schema_name
FROM   information_schema.schemata
LIMIT  10;

다음 표는 샘플 결과를 보여 줍니다.

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

예 - 지정된 데이터베이스 검색

다음 예제 쿼리에서 rdspostgresql은 샘플 데이터베이스입니다.

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

다음 표는 샘플 결과를 보여 줍니다.

	schema_name
1	rdspostgresql

지정된 데이터베이스의 테이블 나열 및 이름별로 테이블 검색

테이블에 대한 메타데이터를 나열하려면 테이블 스키마별로 또는 테이블 이름별로 쿼리할 수 있습니다.

예 - 스키마별로 테이블 나열

다음 쿼리는 rdspostgresql 테이블 스키마를 사용하는 테이블을 나열합니다.

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

다음 표는 샘플 결과를 보여줍니다.

	table_schema	table_name	table_type
1	rdspostgresql	rdspostgresqldb1_public_account	BASE TABLE

예 - 이름별로 테이블 검색

다음 쿼리는 athena1 테이블에 대한 메타데이터 정보를 가져옵니다.

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

다음 표는 샘플 결과를 보여줍니다.

	table_schema	table_name	table_type
1	default	athena1	BASE TABLE

특정 테이블에 대한 파티션 나열

SHOW PARTITIONS table_name을(를) 사용하여 다음 예제처럼 지정된 테이블에 대한 파티션을 나열할 수 있습니다.

SHOW PARTITIONS cloudtrail_logs_test2

$partitions메타데이터 쿼리를 사용하여 특정 테이블의 파티션 번호와 파티션 값을 나열할 수도 있습니다.

예 – $partitions 구문을 사용하여 테이블에 대한 파티션 쿼리

다음 예제 쿼리는 $partitions 구문을 사용하여 cloudtrail_logs_test2 테이블에 대한 파티션을 나열합니다.

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

다음 표는 샘플 결과를 보여 줍니다.

	table_catalog	table_schema	table_name	연도	월	일
1	awsdatacatalog	default	cloudtrail_logs_test2	2020	08	10
2	awsdatacatalog	default	cloudtrail_logs_test2	2020	08	11
3	awsdatacatalog	default	cloudtrail_logs_test2	2020	08	12

모든 테이블에 대한 모든 열 나열

AwsDataCatalog의 모든 테이블이나 AwsDataCatalog의 특정 데이터베이스에 있는 모든 테이블의 모든 열을 나열할 수 있습니다.

• AwsDataCatalog의 모든 데이터베이스에 대한 모든 열을 나열하려면 SELECT * FROM information_schema.columns 쿼리를 사용합니다.

• 결과를 특정 데이터베이스로 제한하려면 WHERE 절에 table_schema='database_name'을 사용합니다.

예 - 특정 데이터베이스의 모든 테이블에 대한 모든 열 나열

다음 예제 쿼리는 데이터베이스 webdata의 모든 테이블에 대한 모든 열을 나열합니다.

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

특정 테이블에서 공통적으로 포함하는 열 나열

데이터베이스의 특정 테이블에서 공통적으로 포함하는 열을 나열할 수 있습니다.

• SELECT column_name FROM information_schema.columns 구문을 사용합니다.

• WHERE 절의 경우 WHERE table_name IN ('table1', 'table2') 구문을 사용합니다.

예 - 동일한 데이터베이스에 있는 두 테이블의 공통 열 나열

다음 예제 쿼리는 table1 및 table2 테이블에서 공통적으로 포함하는 열을 나열합니다.

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

지정된 테이블 또는 뷰에 대한 열 나열 또는 검색

테이블에 대한 모든 열, 뷰에 대한 모든 열을 나열하거나 지정된 데이터베이스와 테이블에서 이름별로 열을 검색할 수 있습니다.

열을 나열하려면 SELECT * 쿼리를 사용합니다. FROM 절에서 information_schema.columns를 지정합니다. WHERE 절에서 table_schema='database_name'을(를) 사용해 데이터베이스를 지정하고 table_name = 'table_name'을(를) 사용해 나열하려는 열이 있는 테이블 또는 뷰를 지정합니다.

예 - 지정된 테이블에 대한 모든 열 나열

다음 예제 쿼리는 rdspostgresqldb1_public_account 테이블에 대한 모든 열을 나열합니다.

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

다음 표는 샘플 결과를 보여 줍니다.

	table_catalog	table_schema	table_name	column_name	ordinal_position	column_default	is_nullable	data_type	comment	extra_info
1	awsdatacatalog	rdspostgresql	rdspostgresqldb1_public_account	비밀번호	1		예	varchar
2	awsdatacatalog	rdspostgresql	rdspostgresqldb1_public_account	user_id	2		예	integer
3	awsdatacatalog	rdspostgresql	rdspostgresqldb1_public_account	created_on	3		예	timestamp
4	awsdatacatalog	rdspostgresql	rdspostgresqldb1_public_account	last_login	4		예	timestamp
5	awsdatacatalog	rdspostgresql	rdspostgresqldb1_public_account	email	5		예	varchar
6	awsdatacatalog	rdspostgresql	rdspostgresqldb1_public_account	username	6		예	varchar

예 - 지정된 뷰에 대한 열 나열

다음 예제 쿼리는 arrayview 뷰에 대한 default 데이터베이스의 모든 열을 나열합니다.

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

다음 표는 샘플 결과를 보여 줍니다.

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

예 - 지정된 데이터베이스 및 테이블에서 이름으로 열 검색

다음 예제 쿼리는 default 데이터베이스의 arrayview 뷰에서 sid 열에 대한 메타데이터를 검색합니다.

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

다음 표는 샘플 결과를 보여줍니다.

	table_catalog	table_schema	table_name	column_name	ordinal_position	column_default	is_nullable	data_type	comment	extra_info
1	awsdatacatalog	default	arrayview	sid	2		예	varchar