Joining different data sources(Sql Server and AWS Athena) via In-DB

Hi Team,

I am joining Sql Server and AWS Athena via In-DB tools. And, in both sources i have applied same formula for date parsing which is working fine independently. but when i join both sources on that date field it says "Both left and right in-db connection should be same"

Formula on Sql Server - concat(cast(day("visitStartTime")as varchar(20)),'-',cast(month("visitStartTime")as varchar(20)),'-',cast(Year("visitStartTime") as varchar(20)))

Formula on Athena - concat(cast(day("visitStartTime")as varchar(20)),'-',cast(month("visitStartTime")as varchar(20)),'-',cast(Year("visitStartTime") as varchar(20)))

What is the reason for that?

Thanks,

Vikas

Amazon S3

Server

Accepted answers

MichalM

@Vikas038

Are you trying to extract a date from a timestamp which is datetime? If so, why don't you try the below function instead?

SQL Server

CONVERT(date, 'visitStartTime')

Athena

trunc('visitStartTime')

All comments

MichalM

@Vikas038

The problem here is that the data sits in two different contexts - SQL Server & AWS Athena. Before you can join the two using the Join InDB, both streams need to be on the same platform. First, I'd think about

Which of the 2 databases you have write access to
Which of the datasets is smaller
Which of the 2 clusters is going to be faster to perform the join

You will then need to stream the data out of one of these and stream it back in into the database you're going to use for the join. Create a temp table after which the join will work. See an example below.

Vikas038

Is there any issue with the concat formula that i mentioned? Because, when i extract data from single source(Sql Server) the output date doesn't allow me to sort it. i extracted daywise data for last three years

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335