Joining CSV Data Without SQL: An IP Geolocation Use Case

&gt;But before you can get there, as a prerequisite you need to enumerate all the field names and desired data types to load it into a specific structured format. Maybe there’s another way?<p>In sqlite, this is just:<p>.mode csv<p>.import data.csv table<p>&gt;<i>When .import is run, its treatment of the first input row depends upon whether the target table already exists. If it does not exist, the table is automatically created and the content of the first input row is used to set the name of all the columns in the table.</i>[0]<p>[0] <a href="https:&#x2F;&#x2F;www.sqlite.org&#x2F;cli.html#importing_files_as_csv_or_other_formats" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.sqlite.org&#x2F;cli.html#importing_files_as_csv_or_ot...</a>

If you really want to, there&#x27;s also the `join` command, which has been part of the standard unix toolset for longer than many of us have been alive.

&gt; a performant join that avoids the hassle of SQL tables.<p>Hm, a hassle is not always the same hassle for everyone. Personally, I like SQL and I enjoy the power it can leverage on all kind of data. As others wrote, working with sqlite for local data processing is a tool I do not want to miss, besides all of the great Unix coreutils and its (mostly GNU) ecosystem.

Personally my weapon-of-choice fort his kind of thing is just raw Powershell. It means all your queries will be simple linear-searches, and powershell is a warty-as-hell language, but the ergonomics aren&#x27;t bad.<p>For example, the &quot;all the cities in Iran&quot; query.<p><pre><code> Import-FromCsv GeoLite2-City-Locations-en.csv | Where-Object {$_.country_name -eq &quot;Iran&quot;} | Select-Object -ExpandProperty city_name </code></pre> You could probably find modules to help with the IP-aware stuff like `cidr_match`, but the real place where it would probably fall over probably performance when joining, since you&#x27;d probably be just be doing O(n*m) convolution operations.

I have done some similar, simpler data wrangling with xsv (<a href="https:&#x2F;&#x2F;github.com&#x2F;BurntSushi&#x2F;xsv">https:&#x2F;&#x2F;github.com&#x2F;BurntSushi&#x2F;xsv</a>) and jq. It could process my 800M rows in a couple of minutes (plus the time to read it out from the database =)

CLI data processing is always fun and cool. But it tends to also be limited in scope and practicality, not to mention performance if you&#x27;re chaining operations between function calls and it needs to re-parse the data every time.<p>If you want to avoid SQL, it&#x27;s really hard to beat a &quot;data frame&quot; data structure for tabular data processing including things like joins.

I do the same with DuckDB and pretty print with tidy-viewer.

Ehhhhhhhh. It hasn&#x27;t really made a super convincing argument not to use SQL. A lot of what is described isn&#x27;t intinsicly a SQL problem, but instead an ETL problem. Eg, the use of complex types can be done by loading a csv into a TEXT table and then test typecasting on a sample set, followed by the full set.<p>And geo indexes are no joke. Using them has made 8hr long SQL queries take seconds.

Wait, you guys aren&#x27;t copy and pasting every CSV by hand into an Excel spreadsheet?

Alternative very appropriate for some uses cases: `vnl-join` from the vnlog toolkit (<a href="https:&#x2F;&#x2F;github.com&#x2F;dkogan&#x2F;vnlog">https:&#x2F;&#x2F;github.com&#x2F;dkogan&#x2F;vnlog</a>). Uses the `join` tool from coreutils (works well, has been around forever), and `vnlog` for nice column labelling

I&#x27;m pretty impressed by how Zed seems to handle the CSV overhead we typically see with standard SQL. That &#x27;gradual slope&#x27; concept and the one-shot query without a ton of preprocessing? Pretty slick. Seeing the CSV parse transition to Zed lake queries resulting in that kind of speed-up is intriguing. Before jumping on board, though, I&#x27;d be curious to see how Zed holds up with even bigger datasets. The CIDR match and join ops are a nice touch, making it feel a tad SQL-like.

Or, you can also use openrefine from Google.<p>Currently mangling a 4 GB file and working with api&#x27;s that use existing data columns to provide output.. Its a great tool.

I would rather create a quick sqllite file. Create some tables with dbeaver and create a csv processor to write to the sqllite file. Max 1 hour.

if you need more tools and power, check out csvkit: <a href="https:&#x2F;&#x2F;csvkit.readthedocs.io&#x2F;en&#x2F;latest&#x2F;tutorial&#x2F;3_power_tools.html#csvjoin-merging-related-data" rel="nofollow noreferrer">https:&#x2F;&#x2F;csvkit.readthedocs.io&#x2F;en&#x2F;latest&#x2F;tutorial&#x2F;3_power_too...</a>

You can join 2 CSV files in a GUI ETL tool like Easy Data Transform in 3 clicks:<p>-drag the 2 CSV files onto the canvas<p>-click &#x27;Join&#x27;<p>-select the 2 columns to join

I needed to do this yesterday. Thankfully I recall a hacker news suggestion to use SQLite. No time at all to get both files imported into tables and successful join queries. I’m glad it was SQL as I needed a few basic transforms (case folding, trim, etc) and conditions.

I‘d rather just use DuckDB and write a few SQLs, and be done.<p>This can be done in the terminal as well.

would be cool to see a writeup converting these to equivalent powershell queries

yes! non standard data wrangling, even if just for fun, is great way to gain a better standing of your workload and hardware.<p>tldr; [de]serialization is your bottleneck, after that it’s general data processing. both are wasting insane levels of cpu cycles. network and disk, when accessed linearly, are free.<p>i remember first looking into this when ec2 i3 came out, only more so since. lambda for burst cpu capacity when you can’t wait 30s for ec2 spot is interesting too.<p><a href="https:&#x2F;&#x2F;nathants.com&#x2F;posts&#x2F;performant-batch-processing-with-bsv-s4-and-presto" rel="nofollow noreferrer">https:&#x2F;&#x2F;nathants.com&#x2F;posts&#x2F;performant-batch-processing-with-...</a>

Please don’t do this for actual work you share with other people. There’s a good reason why pandas exists.