73
edits
Difference between revisions of "ETL"
Jump to navigation
Jump to search
Add sections on snake case and testing ETL jobs
(Add byte-order mark warning) |
(Add sections on snake case and testing ETL jobs) |
||
| Line 10: | Line 10: | ||
A useful tool for writing ETL jobs is [https://github.com/WPRDC/little-lexicographer Little Lexicographer]. While initially designed to just facilitate the writing of data dictionaries (by scanning each column and trying to determine the best type for it, then dumping field names and types into a data dictionary template), Little Lexicographer now also has the ability to output a proposed Marshmallow schema for a CSV file. Its type detection is not perfect, so manual review of the assigned types is necessary. Also, Little Lexicographer is often fooled by seemingly numeric values like ZIP codes; if a value is a code (like a ZIP code or a US Census tract), we treat it as a string. This is especially important in the case of codes that may have leading zeros that would be lost if the value were cast to an integer. | A useful tool for writing ETL jobs is [https://github.com/WPRDC/little-lexicographer Little Lexicographer]. While initially designed to just facilitate the writing of data dictionaries (by scanning each column and trying to determine the best type for it, then dumping field names and types into a data dictionary template), Little Lexicographer now also has the ability to output a proposed Marshmallow schema for a CSV file. Its type detection is not perfect, so manual review of the assigned types is necessary. Also, Little Lexicographer is often fooled by seemingly numeric values like ZIP codes; if a value is a code (like a ZIP code or a US Census tract), we treat it as a string. This is especially important in the case of codes that may have leading zeros that would be lost if the value were cast to an integer. | ||
=== Snake case === | |||
Whenever possible, format column names in [https://en.wikipedia.org/wiki/Snake_case snake case]. This means you should convert everything to lower case and change all spaces and punctuation to underscores (so "FIELD NAME" becomes "field_name" and "# of pirates" should be changed to "number_of_pirates"). Reasons we prefer snake case: 1) Marshmallow already converts field names to snake case to some extent automatically. 2) Snake case field names do not need to be quoted or escaped in PostgreSQL queries (making queries of the CKAN datastore easier). | |||
=== Pitfalls === | === Pitfalls === | ||
The [http://wingolab.org/2017/04/byteordermark byte-order mark] showing up at the beginning of the first field name in your file. Excel seems to add this character by default (unless the user tells it not to). As usual, the moral of the story is "Never use Excel". | The [http://wingolab.org/2017/04/byteordermark byte-order mark] showing up at the beginning of the first field name in your file. Excel seems to add this character by default (unless the user tells it not to). As usual, the moral of the story is "Never use Excel". | ||
== Testing ETL jobs == | |||
Typical initial tests of a rocket-etl job can be invoked like this: | |||
<code>> python launchpad.py engine/payload/<name for publisher/project>/<script name>.py mute to_file</code> | |||
where the <code>mute</code> parameter prevents errors from being sent to the "etl-hell" Slack channel and the to_file parameter writes the output to the default location for the job in question. For instance, the job | |||
<code>> python launchpad.py engine/payload/robopgh/census.py mute to_file</code> | |||
would write its output to a file in the directory <code><PATH TO rocket-etl>/output_files/robopgh/</code>. Note that the namespacing convention routes the output of <code>robopgh</code> jobs to a different directory than that of <code>wormpgh</code> jobs, but if there were two jobs in the <code>robopgh</code> payload folder that write to <code>population.csv</code>, each job would overwrite the output of the other. As this namespacing is for the convenience of testing and development, this level of collision avoidance seems sufficient for now. It's always possible to alter the default output file name by specifying the 'destination_file' parameter in the dict of parameters that define the job (found in, for instance, <code>robopgh/census.py</code> file). | |||
After running the job, examine the output. [https://www.visidata.org/ VisiData] is an excellent tool for rapidly examining and navigating CSV files. As a first step, it's a good idea to go through each column in the output and make sure that the results make sense. Often this can be done by opening the file in VisiData (<code>> vd output_files/robopgh/population.csv</code>) and invoking <code>Shift+F</code> on each column to calculate the histogram of its values. This is a quick way to catch empty columns (which is either a sign that the source file has only null values in it or that there's an error in your ETL code, often because there's a typo in the name of the field you're trying to load from... How [https://marshmallow.readthedocs.io/en/2.x-line/why.html marshmallow] transforms the field names can often be non-intuitive.). | |||
Try to understand what the records in the data represent. Are there any transformations that could be made to help the user understand the data? | |||
Does the set of data as a whole make sense? For instance, look at counts over time (either by grouping records by year+month and aggregating to counts than you can visually scan or by [https://www.visidata.org/docs/graph/ plotting] record counts by date or timestamp). | |||
Are the field names clear? If not, change them to something clearer. Are they unreasonably long when a shorter name would do? Shorten them to something that is still clear. | |||
If you can't figure out something about the data, ask someone else and/or the publisher. | |||
Once you're satisfied with the output data you're getting, you can rerun the job with the <code>test</code> parameter to push the resulting output to the default testbed dataset (a private dataset used for testing ETL jobs): | |||
<code>> python launchpad.py engine/payload/robopgh/census.py mute test</code> | |||
Development instances of rocket-etl should be configured to load to this testbed dataset by default (that is, even if the <code>test</code> parameter is not specified) as a safety feature. The parameter that controls this setting is <code>PRODUCTION</code>, which can be found in the <code>engine/parameters/local_parameters.py</code> file and which should be defined like this: | |||
<code>PRODUCTION = False</code> | |||
Only in production environments should <code>PRODUCTION</code> be set to <code>True</code>. | |||
In a development environment, to run an ETL job and push the results to the production version of the dataset, do this: | |||
<code>> python launchpad.py engine/payload/robopgh/census.py mute production</code> | |||
== Deploying ETL jobs == | |||
(To be written.) | |||
[[Category:Onboarding]] | [[Category:Onboarding]] | ||