0

I want to show the relations between tables in a database stored in Amazon Web Services. My database name is news. From this answer, I run this Python code in Amazon SageMaker

from pyathena import connect
import pandas as pd

conn = connect(s3_staging_dir = '...',
               region_name = '...')

query = """

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_schema = 'news'

"""
    
result = pd.read_sql(query, conn)
result

Then it returns an error

DatabaseError: Execution failed on sql: 

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_schema = 'news'


SYNTAX_ERROR: line 2:6: Table awsdatacatalog.information_schema.table_constraints does not exist
unable to rollback

Could you please elaborate on how to obtain the relation between tables (primary and foreign keys)?

Akira
  • 2,594
  • 3
  • 20
  • 45
  • When you say "a database stored in Amazon Web Services", what database are you referencing? Is this a database that you have installed on an Amazon EC2 instance, or is it using Amazon RDS, or something else? The code seems to be reference `pyathena`, so it is possibly using Amazon Athena? The article you have referenced is concerning MySQL. – John Rotenstein Mar 11 '21 at 23:34
  • @JohnRotenstein I'm using Amazon Athena. I thought `pyathena` is just a bridge to connect to the SQL. I thought it's MySQL since one of MySQL queries runs successfully for this database. – Akira Mar 11 '21 at 23:54
  • It would appear that the code is attempting to connect with Amazon Athena, which is a service that can query data stored in Amazon S3. It still uses a `CREATE TABLE` command to define the 'shape' of the data, but you won't find traditional tables that define the contents of the database. What are you actually wanting to achieve as your end-goal (rather than _how_)? – John Rotenstein Mar 12 '21 at 05:37
  • @JohnRotenstein I have a database containing 25 tables. I would like to see if some of them are linked together by their columns. If it's the case, I will be able to enrich information in a table by extracting information from other linked tables. – Akira Mar 12 '21 at 08:22
  • 1
    Please investigate where the data is _actually_ stored. If it is using Amazon Athena, then there is no concept of "linking tables together" (foreign keys). You can certainly JOIN tables via columns, but they are no defined within the tables themselves. – John Rotenstein Mar 12 '21 at 10:39

1 Answers1

1

There is no such table as INFORMATION_SCHEMA.TABLE_CONSTRAINTS in awsdatacatalog. Also, Amazon Athena doesn't support Primary Keys or Foreign Keys.

Here is a list of things it supports while creating a table:

https://docs.aws.amazon.com/athena/latest/ug/create-table.html

AswinRajaram
  • 1,519
  • 7
  • 18