How to join data from data source using lambda

Question & Answer
,
1

I am encountering an issue when attempting to join data from a data source. Within the logicalTableMap, there is a JoinInstruction field. Could you please advise me on what value I should specify for this field? I am using Lambda functions in conjunction with a MySQL database.

2

Hello @Faysal !

Could you share an example of your join? Also, have you already been sure to double check these requirements for the JoinInstruction?

1 Like
3

i have MySQL database data source. in the database have two tables “orders” and “customer”
i want to join to these two.
how can I join? Should I need to create two datasets “orders” and “customer” and a third dataset to join?
it is possible to join together?

you download my code here:

4

Hi @Faysal - One of the quick solution to use custom sql and put the native sql query for joining. If you are using QuickSight API to create datasets, then in physical map, just use custom sql.

Regards - Sanjeeb

5

thank for your response.

Does this approach work?

first, create two datasets from the table then create 3rd with join instructions

6

Hi @Faysal - It should work, but a poc needs to be done. You can give a try.

Regards - Sanjeeb

7

@Sanjeeb2022 I don’t have much experience working with quicksight. can explain what is "poc "

8

hi @Sanjeeb2022 custom SQL means this implementation: AWS QuickSight DataSet and DataSource CDK Custom Constructs

or If you could provide me with a code example, it would be incredibly helpful for me

9

Hi @Faysal - Custom sql means performing all joins and everything in one SQL statement and put that statement in your logicaltablemap file. I have not tested in Java but i have done it in python.

Regards - Sanjeeb

10

@Sanjeeb2022 Could you please provide some example code in Python or JavaScript?

11

Hi @Faysal - The whole code is bit complex, need to clean up a lot. However we created a physical map file and ensure the custom sql tags .

phydict[k][‘CustomSql’][‘SqlQuery’]="select “+ cols +” from “+logi.loc[i,‘Table Name’]+” where id in (‘1’,3) "

Down the line, let me create some standard code which can be shared to the community.

Regards - Sanjeeb