MagicETL/SQL to join datasets using a comparison between values
I am trying to get a geolocation based on the IPNumber.
Dataset a:
ipaddress
ipnumber (translated from ipaddress)
Dataset b:
lowIPNumber
highIPNumber
Country
Region
City
I would like to add the Country, Region, and City values to an output dataset:
ipaddress
ipnumber
Country
Region
City
where a.ipnumber >= b.lowIPNumber and a.ipnumber <= b.highIPNumber
any ideas on the best approach?
Best Answer
-
Depending on the size of your dataset and the processing needs you can do this in either MySQL (simpler but slower) or MagicETL (more complex, possibly slower depending on dataset size).
MySQL dataflow allows for conditional joins so it's a simple query:
SELECT a.`ipaddress`, a.`ipnumber`, `Country`, `Region`, `City` FROM `dataset_a` as a join `dataset_b` as b on a.ipnumber >= b.lowIPNumber and a.ipnumber <= b.highIPNumber
MagicETL doesn't support conditional joins so you'd need to do a cartesian join and then filter after the fact. You'd have an Add Column tile to create a new column (call it 'Join Column') with a value of 1 for each, then do a join based on the join column then after that you'd do your filter in a filter tile (ipnumber >= lowIPNumber and ipnumber <= highIPNumber)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
Depending on the size of your dataset and the processing needs you can do this in either MySQL (simpler but slower) or MagicETL (more complex, possibly slower depending on dataset size).
MySQL dataflow allows for conditional joins so it's a simple query:
SELECT a.`ipaddress`, a.`ipnumber`, `Country`, `Region`, `City` FROM `dataset_a` as a join `dataset_b` as b on a.ipnumber >= b.lowIPNumber and a.ipnumber <= b.highIPNumber
MagicETL doesn't support conditional joins so you'd need to do a cartesian join and then filter after the fact. You'd have an Add Column tile to create a new column (call it 'Join Column') with a value of 1 for each, then do a join based on the join column then after that you'd do your filter in a filter tile (ipnumber >= lowIPNumber and ipnumber <= highIPNumber)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Way too big for a cartesian join. (18M records x 2.9M records)!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive