Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

Highlighted
Asteroid

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

clipboard_image_0.png

 

What is the reason for that?

 

Thanks,

Vikas

Highlighted
Alteryx
Alteryx

@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.

 

join-in-db.png

Highlighted
Asteroid

 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 

Highlighted
Magnetar
Magnetar

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

Highlighted
Alteryx
Alteryx

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

 

 

Labels