Alternative very appropriate for some uses cases: `vnl-join` from the vnlog toolkit (https://github.com/dkogan/vnlog). Uses the `join` tool from coreutils (works well, has been around forever), and `vnlog` for nice column labelling
Joining CSV Data Without SQL: An IP Geolocation Use Case
21–30 of 41 posts
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#22Or, you can also use openrefine from Google. Currently mangling a 4 GB file and working with api's that use existing data columns to provide output.. Its a great tool.
How does it work for much larger files?
Have found that to be the most succesful amongst a horde of other tools tried.. I have had no problems with file as big as 8-10 gigs as I can allocate more memory to the program as I see fit.
Honestly, given that I can use grel/clojure/python inside to clean up and mangle data seems to make it the swiss knife of data segmentation/cleanup.
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#23> a performant join that avoids the hassle of SQL tables. 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.
I wasn't aware, but some developers do seem to be allergic to SQL at all cost and would go to great lengths to avoid it just because.
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#24>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…
I can't live without this anymore! However, occasionally sqlite won't quite guess the type affinity as I'd hoped for a column, then I do have to resort to enumerating all the types.
I find it slightly annoying to have to switch mode back to something reasonable again, since mode impacts query results as well as imports.
Despite doing this every few weeks, I can never remember what the commands are! The Zui might improve this workflow for me a bit. Worth a shot!
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#25Personally 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 You could probably find modu…
nushell is also amazing for exploring data quickly like this! I can't use it as a daily driver shell, but I just call it directly from whatever other shell I'm already in and then ^D back to my prior session when I'm done exploring. Works great and lets me visualize realllly nicely.
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#26If you really want to, there's also the `join` command, which has been part of the standard unix toolset for longer than many of us have been alive.
As a sysadmin/SRE who occasionally has to join data, I am very intrigued. Thanks for the callout!
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#27I 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.
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#28if you need more tools and power, check out csvkit: https://csvkit.readthedocs.io/en/latest/tutorial/3_power_too...
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#29Wait, you guys aren't copy and pasting every CSV by hand into an Excel spreadsheet?
Re: Joining CSV Data Without SQL: An IP Geolocation Use Case
#30Earlier quoted context omitted.
How does it work for much larger files?
Have found that to be the most succesful amongst a horde of other tools tried.. I have had no problems with file as big as 8-10 gigs as I can allocate more memory to the program as I see fit. Honestly, given that I can use grel/clojure/python inside to clean up and mangle data seems to make it the swiss knife of data segmentation/cleanup.
Nice, thanks for sharing!