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

[Vikas038]

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

[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 

1. Which of the 2 databases you have write access to
2. Which of the datasets is smaller
3. 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 

[estherb47]

I'm wondering if the data type that you're casting as is problematic to the sort.

Is it possible for you to stream the data into Alteryx, and then do your manipulations and joins? I find that method often easier and much less problematic than the In DB options.

Cheers!

Esther

[MichalM · Accepted solution]

@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')