Joining CSV Data Without SQL: An IP Geolocation Use Case
1–10 of 41 posts
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#2would be cool to see a writeup converting these to equivalent powershell queries
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#3I have done some similar, simpler data wrangling with xsv (https://github.com/BurntSushi/xsv) and jq. It could process my 800M rows in a couple of minutes (plus the time to read it out from the database =)
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#4CLI data processing is always fun and cool. But it tends to also be limited in scope and practicality, not to mention performance if you're chaining operations between function calls and it needs to re-parse the data every time.
If you want to avoid SQL, it's really hard to beat a "data frame" data structure for tabular data processing including things like joins.
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#5>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?
In sqlite, this is just:
.mode csv
.import data.csv table
>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.[0]
[0] https://www.sqlite.org/cli.html#importing_files_as_csv_or_ot...
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#6I do the same with DuckDB and pretty print with tidy-viewer.
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#7Personally 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't bad.
For example, the "all the cities in Iran" query.
Import-FromCsv GeoLite2-City-Locations-en.csv |
Where-Object {$_.country_name -eq "Iran"} |
Select-Object -ExpandProperty city_name
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#8Please don’t do this for actual work you share with other people. There’s a good reason why pandas exists.
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#9Ehhhhhhhh. It hasn't really made a super convincing argument not to use SQL. A lot of what is described isn'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.
And geo indexes are no joke. Using them has made 8hr long SQL queries take seconds.
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#10I'm pretty impressed by how Zed seems to handle the CSV overhead we typically see with standard SQL. That 'gradual slope' 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'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.