Query in Athena using extraction timestamp with current system date

0

Hi Experts, We have replicated DB from SQL server to AWS. We need to extract latest table data using extraction_timestamp. For every insert and update in SQL DB it is being synchronized with AWS S3 with extraction timestamp.

Regards, Srinivasan

asked 2 months ago193 views
2 Answers
0

There are considerations to keep in mind when working with timestamp data in Athena. Such as expected timestamp data format, precision, partition and more. Refer to these resources for more information query-table-athena-timestamp and data-types-timestamps in the AWS documentation.

profile pictureAWS
BezuW
answered 2 months ago
0

As per this piece of documentation: https://docs.aws.amazon.com/prescriptive-guidance/latest/dynamodb-full-table-copy-options/amazon-s3.html

  • Configure DMS for SQL Server to Amazon Aurora Replication: Start by configuring DMS to replicate data from your SQL Server database to your Amazon Aurora database. DMS supports replication from SQL Server and can capture changes at a table row level with timestamps.
  • Replicate Change Data to S3: Configure DMS to replicate the change data to an S3 bucket. This will capture the insert/update changes along with the extraction timestamp in S3. By doing this, you'll have a comprehensive record of all changes made to your SQL Server database.
  • Query S3 Data Location: Once the data is replicated to S3, you can query the S3 data location to fetch the latest records based on the extraction timestamp. You have several options for querying S3 data (i.e. AWS Glue):

glue_crawler = GlueCrawler.create(
    Name="sql_server_changes",
    DatabaseName="default", 
    TablePrefix="changes_",
    Role=role,
    Configuration={
        "CrawlerOutput": {
            "Partitions": {
                "AddOrUpdateBehavior": "InheritFromTable"
            }
        }
    }
)
profile picture
EXPERT
answered 2 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions