15 ms·

How ProPublica Illinois Uses GNU Make to Load Data

191 points by danso 8y ago

brudgers 8y ago
Original, https://www.propublica.org/nerds/gnu-make-illinois-campaign-finance-data-david-eads-propublica-illinois#146596 https://www.propublica.org/nerds/gnu-make-illinois-campaign-...
- dang 8y ago
  OK, we've changed to that from https://source.opennews.org/articles/how-propublica-illinois-uses-gnu-make-load-14gb-da/ https://source.opennews.org/articles/how-propublica-illinois.... Thanks!
- yjftsjthsd-h 8y ago
  I appreciate that it's under a /nerds path:)
  - catacombs 8y ago
    It's cute.
danso 8y ago
> The first is that we use Aria2 to handle FTP duties. Earlier versions of the script used other FTP clients that were either slow as molasses or painful to use. After some trial and error, I found Aria2 did the job better than lftp (which is fast but fussy) or good old ftp (which is both slow and fussy). I also found some incantations that took download times from roughly an hour to less than 20 minutes. Tangential question: is it possible to use wget for ftp duties? Though may be additional FTP-specific functionality in `aria2c` of course: https://serverfault.com/questions/25199/using-wget-to-recursively-download-whole-ftp-directories https://serverfault.com/questions/25199/using-wget-to-recurs...
- na85 8y ago
  Have a look at the wget manual. There are lots of FTP-related options.
  - jolmg 8y ago
    Perhaps he meant in their specific scenario, since he linked a page that shows use of wget for FTP.
    - danso 8y ago
      Yes, I should have specified that I was interested in what aria2 provides for FTP in addition to what a more ubiquitous tool like wget seemingly has. u/rasz says aria2 allows multi-connections, so that seems sensible: https://news.ycombinator.com/item?id=17508858 https://news.ycombinator.com/item?id=17508858
- rasz 8y ago
  aria is multi connection(aria2c -x5 means five concurrent), thats the main reason for speed bump
  - flukus 8y ago
    Does this increase speed when it's only downloading a single file at a time? It might be better off using makes multi process (make -j 5) to be able to process data while still loading other data.
    - therein 8y ago
      Each connection requests a different range within the same file and they download together.
- deleted 8y ago
  [deleted]
peterwwillis 8y ago
Since they have "A Note about Security", how about locking down that Python environment? - Add hashes to their already-pinned requirements.txt deps: https://pip.pypa.io/en/stable/reference/pip_install/#hash-checking-mode https://pip.pypa.io/en/stable/reference/pip_install/#hash-ch... - Add a Makefile entry to run `[ -d your-environment ] || ( virtualenv your-environment && . your-environment/bin/activate && ./your-environment/bin/pip install --no-deps --require-hashes -r requirements.txt )`
stakhanov 8y ago
Was that supposed to say Petabytes? Gigabytes is really not that impressive.
- drb91 8y ago
  It doesn’t seem like the size is supposed to be impressive, although I do not know why it is in the title. This is about the use of make.
  - stakhanov 8y ago
    ...well, that was what I was trying to point out.
deleted 8y ago
[deleted]
rainbowmverse 8y ago
A lot of comments in here are poking fun at how little data it is relative to a commercial data mining operation. The data they process and what they do with it is worth more to society than any number of petabytes crunched to target ads. Processing petty petabytes is not praiseworthy. If you focus on the headline, you'll miss the point. The point is they used open source technology to process public data for reporting once the government stopped updating its own tools.
- VectorLock 8y ago
  Pettybytes. I like that.
  - deleted 8y ago
    [deleted]
- dang 8y ago
  Maybe we'll just take the gigabytes per day bit out of the title so it doesn't trigger people.
  - deleted 8y ago
    [deleted]
  - virtuabhi 8y ago
    Would it be possible to put the gigabytes back in the title? In general, ETL of gigabytes of data can involve complicated operations, e.g., use of statistical models. And, the utility of data is not determined by the size of data. One has to be a pretty petty person to make fun of this article for data sizes.
    - rainbowmverse 8y ago
      That was almost every comment before I posted. All of them seem to be grayed out now.
    - dang 8y ago
crazy_monkey 8y ago
Are you sure someone is not just streaming watching Netflix on your data plan? ... Honestly, I've seen Excel spread sheets load 10x more data than that! :D
usgroup 8y ago
Tbh I would have opted for Jenkins with declarative pipelines in his situation. Then you get logging , events , cron , CI , all more or less out of the box . IMO, this is a bad use case for make.
rockmeamedee 8y ago
Make is often brought out for data, "single machine ETL" jobs, but for big, complicated (and iterative) workflows it doesn't feel good enough to me. What do you folks use? Drake, "make for data" https://github.com/Factual/drake https://github.com/Factual/drake seems ok, but doesn't have "batch" jobs, (aka "pattern rules") where you can do every file in a directory matching a pattern. Others have come up with different swiss army knives but nothing ever sticks for me, it usually ends up as a single Makefile with eg 3 targets that call a bunch of shell scripts. The whole thing would be configurable to build from scratch, but not well set up to do incremental ETL on a per file basis, after I eg delete some extraneous rows in one file, clean up a column, redownload a folder, or add files to a dataset.
- reacharavindh 8y ago
  Just today, I used xargs instead of spending a lot of time building a batching script in Python. I wanted to launch a bunch of processes in a queue but only execute 10 of them in parallel at any time. Here is a skeleton of what I came up with. find $(pwd) -mindepth 1 -maxdepth 1 -type d -name ".zfs" -prune -o -type d -print0|xargs -0 -P 2 -I {} echo {} where, $(pwd) indicates the starting point of the listing of directories -mindepth 1 makes sure current directory is not listed once again. -maxdepth 1 makes sure the list does not get recursive -type d -name - only directories and list names ".zfs" -prune - makes it ignore .zfs (snapshot directories) -print0 - makes sure to print results without newlines. just -print will print one result per line xargs -0 will take care of processing out spaces or newlines in the input stream -P 2 — run two processes at once in parallel -I {} says that replace {} in teh subsequent command from stdin piped into xargs echo {} will be echo dir1 and then echo dir2 etc That's just an example to show that we can do a lot with standard Unix tools before bringing in the external sophistication for data related tasks.
  - icebraining 8y ago
    And with GNU parallel, which can take the place of xargs, you can even distribute that job across multiple machines easily (as long as they're accessible by SSH).
pdkl95 8y ago
> [...] --ftp-passwd="$(ILCAMPAIGNCASH_FTP_PASSWD)" ftp://ftp.elections.il.gov/[...] Is that using traditional (plaintext) FTP? Is it listening on port 21? ~ $ ftp ftp.elections.il.gov Connected to ftp.elections.il.gov (163.191.231.32). 220-Microsoft FTP Service 220 SBE Name (ftp.elections.il.gov): ^C It looks like they are sending their password in plaintext. aria2 supports SFTP, so they should really talk to elections.il.gov about moving to SFTP or any other protocol that doesn't send the password in plaintext.
- jwilk 8y ago
  This FTP server supports TLS: 211-Extended features supported: LANG EN* UTF8 AUTH TLS;TLS-C;SSL;TLS-P; PBSZ PROT C;P; CCC HOST SIZE MDTM REST STREAM I didn't check if aria2 would actually use it, but I doubt it. Re alternatives to FTP: as far as only downloads are concerned, HTTPS should be way easier to set up than SFTP.
- danso 8y ago
  I imagine there would be other systems (state-owned and private) that use the FTP server, and maybe in a way that changing protocols is inexplicably full of friction. I wonder why the elections server, assuming it only contains records legal to distribute to the public, is even password protected. Maybe it was a policy when govt bandwidth was scarce. California, for example, has campaign finance data on a public webserver: https://www.californiacivicdata.org/ https://www.californiacivicdata.org/ And the FEC has an API, but has long had the data hosted on public FTP: https://classic.fec.gov/finance/disclosure/ftp_download.shtml https://classic.fec.gov/finance/disclosure/ftp_download.shtm...
deleted 8y ago
[deleted]
dev_dull 8y ago
Can somehow help me understand the advantages of Make over a bash script? Isn’t bash superior in almost every way?
- outworlder 8y ago
  If you are creating files, and these files depends on other files, Make will do the dependency resolution for you. This is difficult to do with Bash and a huge reason to use Make. Once you start abusing the ".PHONY" targets, the value starts to decrease.
- duckerude 8y ago
  Make has a dependency system. You can tell it how to create file A, and that it depends on file B, and tell it how to create file B. Then if you request file A, it will check whether file B exists, and create it first if it's missing or outdated. That's very valuable for building things. If you change a file, you only need to re-build the files that could reasonably be affected by the change. And things happen in the right order without micro-managing.
- Pissompons 8y ago
  They can produce the same result but they do it in different ways and require you to express it in different ways. Make has you describe a graph of outputs and how to produce them. It then traverses the graph to produce the requested output. Bash is just a regular sequence of commands, with functions and loops if you wish. If the pipeline you need to run can easily be turned into a dependency graph, I think make is a great fit. It's easy to use, comes with most of what you need built in and has some fun extras, like -jXXX, which allows you to parallelise things and built in caching so you don't regenerate the same asset twice if you don't need to. You can do all that in bash but you'll have to write it yourself, which takes time you could spend on other things.
- tejtm 8y ago
  Make is an excellent way to automatically decide to run your bash scripts (and other scripts, shell commands & executables) or not, all depending on if the existing output is newer than the available input
bazizbaziz 8y ago
Minor nitpick about their exit code technique [0]: The command checks if the table exists, but it does not appear to re-run if the source file has been updated. Usually with Make you expect it to re-run the database load if the source file has changed. It's better to use empty targets [1] to track when the file has last been loaded and re-run if the dependency has been changed. [0] https://github.com/propublica/ilcampaigncash/blob/master/Makefile#L27 https://github.com/propublica/ilcampaigncash/blob/master/Mak... [1] https://www.gnu.org/software/make/manual/html_node/Empty-Targets.html https://www.gnu.org/software/make/manual/html_node/Empty-Tar...
beebmam 8y ago
This is exactly the kind of purpose I love seeing open source tools used for. Kudos to propublica for leveraging open source to improve their ability to function!
- catacombs 8y ago
  While it's nice to see Propublica use open source software, keep in mind dozens and dozens of other news organizations use the same tools.
lasermike026 8y ago
I really like make! I use it almost every day. I like it for the structure and simplicity. I don't use it for everything. I plan to use it for the foreseeable future. Why do I like make over shell scripting (sometimes) is that enforces structure. Shell scripts can turn into a real hairball. When I did ruby I really enjoyed using rake.
chemicalcrux 8y ago
I did something like this a few years ago! I needed to do a bunch of transformations and measurements of data that came in on a regular basis. Make was a perfect fit - I could test the whole process with a single command, cleaning either just the result data, or nuking everything to make sure it pulled stuff in properly. I spent some time trying to write my own processing system in Python before realizing this was a familiar task...
rpz 8y ago
Shoulda used kdb!
stakhanov 8y ago
On the whole debate revolving around gigabytes in the title, I'd like to add: There's a well-substantiated linguistic theory revolving around "maxims of conversation". Maxims of conversation are so strongly universal among the speakers of a given language that they become part of the implied meaning of a conversational act. For example the maxim of cooperativitiy implies that when a person sitting in a cold room next to a window is spoken to by a person sitting further from the window and is being told "It's a bit chilly, isn't it", they can take it to mean "Please close the window". https://en.wikipedia.org/wiki/Implicature#Conversational_implicature https://en.wikipedia.org/wiki/Implicature#Conversational_imp... Similarly, there are certain maxims of conversation which are part of the language game inherent in the formulation of the title of a blogpost. They are kind of assumed to be boasting about something. So when somebody says "We figured out a way to load a gigabyte's worth of data into a database in a single day" then the being-boastful-about-something maxim is violated. That's why it triggered so many people. And pointing out that this is not something to be boastful about is a perfectly valid thing to do to keep certain facts straight. ...just saying. But, by all means, if you get a thrill out of it, keep downvoting me.

191 points by danso 8y ago

Original, https://www.propublica.org/nerds/gnu-make-illinois-campaign-finance-data-david-eads-propublica-illinois#146596 https://www.propublica.org/nerds/gnu-make-illinois-campaign-...

dang 8y ago
OK, we've changed to that from https://source.opennews.org/articles/how-propublica-illinois-uses-gnu-make-load-14gb-da/ https://source.opennews.org/articles/how-propublica-illinois.... Thanks!
yjftsjthsd-h 8y ago
I appreciate that it's under a /nerds path:)
- catacombs 8y ago
  It's cute.

danso 8y ago

> The first is that we use Aria2 to handle FTP duties. Earlier versions of the script used other FTP clients that were either slow as molasses or painful to use. After some trial and error, I found Aria2 did the job better than lftp (which is fast but fussy) or good old ftp (which is both slow and fussy). I also found some incantations that took download times from roughly an hour to less than 20 minutes. Tangential question: is it possible to use wget for ftp duties? Though may be additional FTP-specific functionality in `aria2c` of course: https://serverfault.com/questions/25199/using-wget-to-recursively-download-whole-ftp-directories https://serverfault.com/questions/25199/using-wget-to-recurs...

na85 8y ago
Have a look at the wget manual. There are lots of FTP-related options.
- jolmg 8y ago
  Perhaps he meant in their specific scenario, since he linked a page that shows use of wget for FTP.
  - danso 8y ago
    Yes, I should have specified that I was interested in what aria2 provides for FTP in addition to what a more ubiquitous tool like wget seemingly has. u/rasz says aria2 allows multi-connections, so that seems sensible: https://news.ycombinator.com/item?id=17508858 https://news.ycombinator.com/item?id=17508858
rasz 8y ago
aria is multi connection(aria2c -x5 means five concurrent), thats the main reason for speed bump
- flukus 8y ago
  Does this increase speed when it's only downloading a single file at a time? It might be better off using makes multi process (make -j 5) to be able to process data while still loading other data.
  - therein 8y ago
    Each connection requests a different range within the same file and they download together.
deleted 8y ago
[deleted]

peterwwillis 8y ago

Since they have "A Note about Security", how about locking down that Python environment? - Add hashes to their already-pinned requirements.txt deps: https://pip.pypa.io/en/stable/reference/pip_install/#hash-checking-mode https://pip.pypa.io/en/stable/reference/pip_install/#hash-ch... - Add a Makefile entry to run `[ -d your-environment ] || ( virtualenv your-environment && . your-environment/bin/activate && ./your-environment/bin/pip install --no-deps --require-hashes -r requirements.txt )`

stakhanov 8y ago

Was that supposed to say Petabytes? Gigabytes is really not that impressive.

drb91 8y ago
It doesn’t seem like the size is supposed to be impressive, although I do not know why it is in the title. This is about the use of make.
- stakhanov 8y ago
  ...well, that was what I was trying to point out.

deleted 8y ago

[deleted]

rainbowmverse 8y ago

A lot of comments in here are poking fun at how little data it is relative to a commercial data mining operation. The data they process and what they do with it is worth more to society than any number of petabytes crunched to target ads. Processing petty petabytes is not praiseworthy. If you focus on the headline, you'll miss the point. The point is they used open source technology to process public data for reporting once the government stopped updating its own tools.

VectorLock 8y ago
Pettybytes. I like that.
- deleted 8y ago
  [deleted]
dang 8y ago
Maybe we'll just take the gigabytes per day bit out of the title so it doesn't trigger people.
- deleted 8y ago
  [deleted]
- virtuabhi 8y ago
  Would it be possible to put the gigabytes back in the title? In general, ETL of gigabytes of data can involve complicated operations, e.g., use of statistical models. And, the utility of data is not determined by the size of data. One has to be a pretty petty person to make fun of this article for data sizes.
  - rainbowmverse 8y ago
    That was almost every comment before I posted. All of them seem to be grayed out now.
  - dang 8y ago

crazy_monkey 8y ago

Are you sure someone is not just streaming watching Netflix on your data plan? ... Honestly, I've seen Excel spread sheets load 10x more data than that! :D

usgroup 8y ago

Tbh I would have opted for Jenkins with declarative pipelines in his situation. Then you get logging , events , cron , CI , all more or less out of the box . IMO, this is a bad use case for make.

rockmeamedee 8y ago

Make is often brought out for data, "single machine ETL" jobs, but for big, complicated (and iterative) workflows it doesn't feel good enough to me. What do you folks use? Drake, "make for data" https://github.com/Factual/drake https://github.com/Factual/drake seems ok, but doesn't have "batch" jobs, (aka "pattern rules") where you can do every file in a directory matching a pattern. Others have come up with different swiss army knives but nothing ever sticks for me, it usually ends up as a single Makefile with eg 3 targets that call a bunch of shell scripts. The whole thing would be configurable to build from scratch, but not well set up to do incremental ETL on a per file basis, after I eg delete some extraneous rows in one file, clean up a column, redownload a folder, or add files to a dataset.

reacharavindh 8y ago
Just today, I used xargs instead of spending a lot of time building a batching script in Python. I wanted to launch a bunch of processes in a queue but only execute 10 of them in parallel at any time. Here is a skeleton of what I came up with. find $(pwd) -mindepth 1 -maxdepth 1 -type d -name ".zfs" -prune -o -type d -print0|xargs -0 -P 2 -I {} echo {} where, $(pwd) indicates the starting point of the listing of directories -mindepth 1 makes sure current directory is not listed once again. -maxdepth 1 makes sure the list does not get recursive -type d -name - only directories and list names ".zfs" -prune - makes it ignore .zfs (snapshot directories) -print0 - makes sure to print results without newlines. just -print will print one result per line xargs -0 will take care of processing out spaces or newlines in the input stream -P 2 — run two processes at once in parallel -I {} says that replace {} in teh subsequent command from stdin piped into xargs echo {} will be echo dir1 and then echo dir2 etc That's just an example to show that we can do a lot with standard Unix tools before bringing in the external sophistication for data related tasks.
- icebraining 8y ago
  And with GNU parallel, which can take the place of xargs, you can even distribute that job across multiple machines easily (as long as they're accessible by SSH).

pdkl95 8y ago

> [...] --ftp-passwd="$(ILCAMPAIGNCASH_FTP_PASSWD)" ftp://ftp.elections.il.gov/[...] Is that using traditional (plaintext) FTP? Is it listening on port 21? ~ $ ftp ftp.elections.il.gov Connected to ftp.elections.il.gov (163.191.231.32). 220-Microsoft FTP Service 220 SBE Name (ftp.elections.il.gov): ^C It looks like they are sending their password in plaintext. aria2 supports SFTP, so they should really talk to elections.il.gov about moving to SFTP or any other protocol that doesn't send the password in plaintext.

jwilk 8y ago
This FTP server supports TLS: 211-Extended features supported: LANG EN* UTF8 AUTH TLS;TLS-C;SSL;TLS-P; PBSZ PROT C;P; CCC HOST SIZE MDTM REST STREAM I didn't check if aria2 would actually use it, but I doubt it. Re alternatives to FTP: as far as only downloads are concerned, HTTPS should be way easier to set up than SFTP.
danso 8y ago
I imagine there would be other systems (state-owned and private) that use the FTP server, and maybe in a way that changing protocols is inexplicably full of friction. I wonder why the elections server, assuming it only contains records legal to distribute to the public, is even password protected. Maybe it was a policy when govt bandwidth was scarce. California, for example, has campaign finance data on a public webserver: https://www.californiacivicdata.org/ https://www.californiacivicdata.org/ And the FEC has an API, but has long had the data hosted on public FTP: https://classic.fec.gov/finance/disclosure/ftp_download.shtml https://classic.fec.gov/finance/disclosure/ftp_download.shtm...

deleted 8y ago

[deleted]

dev_dull 8y ago

Can somehow help me understand the advantages of Make over a bash script? Isn’t bash superior in almost every way?

outworlder 8y ago
If you are creating files, and these files depends on other files, Make will do the dependency resolution for you. This is difficult to do with Bash and a huge reason to use Make. Once you start abusing the ".PHONY" targets, the value starts to decrease.
duckerude 8y ago
Make has a dependency system. You can tell it how to create file A, and that it depends on file B, and tell it how to create file B. Then if you request file A, it will check whether file B exists, and create it first if it's missing or outdated. That's very valuable for building things. If you change a file, you only need to re-build the files that could reasonably be affected by the change. And things happen in the right order without micro-managing.
Pissompons 8y ago
They can produce the same result but they do it in different ways and require you to express it in different ways. Make has you describe a graph of outputs and how to produce them. It then traverses the graph to produce the requested output. Bash is just a regular sequence of commands, with functions and loops if you wish. If the pipeline you need to run can easily be turned into a dependency graph, I think make is a great fit. It's easy to use, comes with most of what you need built in and has some fun extras, like -jXXX, which allows you to parallelise things and built in caching so you don't regenerate the same asset twice if you don't need to. You can do all that in bash but you'll have to write it yourself, which takes time you could spend on other things.
tejtm 8y ago
Make is an excellent way to automatically decide to run your bash scripts (and other scripts, shell commands & executables) or not, all depending on if the existing output is newer than the available input

bazizbaziz 8y ago

Minor nitpick about their exit code technique [0]: The command checks if the table exists, but it does not appear to re-run if the source file has been updated. Usually with Make you expect it to re-run the database load if the source file has changed. It's better to use empty targets [1] to track when the file has last been loaded and re-run if the dependency has been changed. [0] https://github.com/propublica/ilcampaigncash/blob/master/Makefile#L27 https://github.com/propublica/ilcampaigncash/blob/master/Mak... [1] https://www.gnu.org/software/make/manual/html_node/Empty-Targets.html https://www.gnu.org/software/make/manual/html_node/Empty-Tar...

beebmam 8y ago

This is exactly the kind of purpose I love seeing open source tools used for. Kudos to propublica for leveraging open source to improve their ability to function!

catacombs 8y ago
While it's nice to see Propublica use open source software, keep in mind dozens and dozens of other news organizations use the same tools.

lasermike026 8y ago

I really like make! I use it almost every day. I like it for the structure and simplicity. I don't use it for everything. I plan to use it for the foreseeable future. Why do I like make over shell scripting (sometimes) is that enforces structure. Shell scripts can turn into a real hairball. When I did ruby I really enjoyed using rake.

chemicalcrux 8y ago

I did something like this a few years ago! I needed to do a bunch of transformations and measurements of data that came in on a regular basis. Make was a perfect fit - I could test the whole process with a single command, cleaning either just the result data, or nuking everything to make sure it pulled stuff in properly. I spent some time trying to write my own processing system in Python before realizing this was a familiar task...

rpz 8y ago

Shoulda used kdb!

stakhanov 8y ago

On the whole debate revolving around gigabytes in the title, I'd like to add: There's a well-substantiated linguistic theory revolving around "maxims of conversation". Maxims of conversation are so strongly universal among the speakers of a given language that they become part of the implied meaning of a conversational act. For example the maxim of cooperativitiy implies that when a person sitting in a cold room next to a window is spoken to by a person sitting further from the window and is being told "It's a bit chilly, isn't it", they can take it to mean "Please close the window". https://en.wikipedia.org/wiki/Implicature#Conversational_implicature https://en.wikipedia.org/wiki/Implicature#Conversational_imp... Similarly, there are certain maxims of conversation which are part of the language game inherent in the formulation of the title of a blogpost. They are kind of assumed to be boasting about something. So when somebody says "We figured out a way to load a gigabyte's worth of data into a database in a single day" then the being-boastful-about-something maxim is violated. That's why it triggered so many people. And pointing out that this is not something to be boastful about is a perfectly valid thing to do to keep certain facts straight. ...just saying. But, by all means, if you get a thrill out of it, keep downvoting me.