Joining different data sources(Sql Server and AWS Athena) via In-DB
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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')
