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.5K Product Ideas
- 1.5K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 278 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 89 SQL DataFlows
- 560 Datasets
- 2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 575 Beast Mode
- 13 App Studio
- 28 Variables
- 582 Automate
- 141 Apps
- 414 APIs & Domo Developer
- 26 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 356 Distribute
- 95 Domo Everywhere
- 259 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 89 Community Announcements
- 4.8K Archive