SQL views for exporting data

[hepplerj]

To keep the export of data as simple as possible, we'll create PostgreSQL views to set up export tables. We can use these views to either write directly from Postgres or use a GUI like Postico to export to a CSV file.

Here's an idea of how we could do this:

CREATE VIEW export_relcensus_denominations
AS SELECT 
	year, 
	denomination_id, 
	name, 
	short_name, 
	family_census, 
	family_arda, 
	family_relec, 
	id
FROM relcensus.denominations;

If this seems like a sensible way forward, and a way that works for the various kinds of data and joins we'd like to create, then I'd suggest we also settle on a syntax to keep things organized. Given a view called export_relcensus_denominations, the general view syntax might be the following:

• export_: This tells us that a view is set up for exporting.
• _relecensus: This tells us which schema or project an export belongs to.
• _denominations: This is the name of the table you are exporting. If it's explicitly a single table (i.e., there are no JOIN statements) it should be the same name of the table you're exporting. If there is a JOIN, the name of the view should be briefly descriptive of the data you are exporting.

In other words, our general syntax for defining a view is export_<project>_<table>.

[hepplerj]

As a next step, I'll work on creating the additional views to make sure using views will actually work for us. Any wrinkles I run into I'll raise here for discussion or troubleshooting.

[lmullen]

A couple of off the cuff suggestions.

• Would it make sense to put these into a schema? E.g. export_relcensus.denominations?
• Since the order of rows is not guaranteed by the database, it would be good to explicitly sort each time. So, in the example above, probably SORT year, name or something like that.

[hepplerj]

1. Sure, I think that's a good idea. I'll make some modifications to my tests and make sure that'll work.
2. Good point about sorting, I'll add that in.

[hepplerj]

So, our new view would instead look something like:

CREATE VIEW export_relcensus.denominations
AS SELECT 
	year, 
	denomination_id, 
	name, 
	short_name, 
	family_census, 
	family_arda, 
	family_relec, 
	id
FROM relcensus.denominations
ORDER BY year, name;

[hepplerj]

Just want to bring @qtrinh2 into the conversation. Do you have any preferences or thoughts about how we set up and name these Postgres views? The idea here is we have some sensibly-named views that we can use for exporting datasets as a CSV from Religious Ecologies that we will "release" -- that is, from the RelEc website people will be able to discover and download these datasets without having to mess with our data API. I'm aiming for these to be easily exported from a GUI interface like Postico or DataGrip.

Right now this is limited to Religious Ecologies, but I could envision ways were we might have similar setups for future data-driven projects.

[qtrinh2]

@hepplerj, if these views are expected to only source data from one existing schema, then I'd probably prefer them to be defined in that one schema named export_<view_name> or something. If there's a possibility of having views that source data from multiple schemas, then a dedicated schema for these views could make more sense.

Excluding SQL clients of people directly connecting to our databases, I'd like to know how these views would be used by our websites. e.g. if there's a link on our website that eventually invokes SELECT from the views, then I'd consider making materialized views instead of "normal" views or at least figure out server-side caching if available.

[hepplerj]

Good point about the materialized views. At this stage I don't anticipate our websites using these views as part of their operations. My thinking here was that these export_<view> views would always be for someone on a project team to generate a data export that we'd subsequently release on GitHub (and/or deposit in a data repository). So the only people who would ever have access to the views would be anyone who has database access already. That's to say I don't imagine a serious performance issue since the views won't be accessed all that often. But I also wouldn't argue against it; if that seems like a better way forward, and we just refresh the materialized view before an export, that seems fine to me.

To your point about a dedicated schema: perhaps we should do this. While we're not at the moment, I suppose there could be cases where we join, say, spatial data to a dataset so we could export a geojson file. There's no objections from me for holding the exports in a schema to keep things organized and maintainable.

[hepplerj]

Truth be told, I imagine the only one actually generating data exports will be me or Savannah. I don't think there will be a case where we'll have to automate this or there would ever be a serious workload to it -- these won't happen all that often.

[qtrinh2]

If only real people are using these views, then I'm fine with normal views.

Can you create the new schema in the development database. I want to explore proper roles for users instead of managing individual privileges. So like a data exporting role that would have read-only privileges to all other apiary schemas and read/write privileges on the new data exports schema.

[hepplerj]

Sure thing, I'll get on the dev db today with some test exports we can mess around with.

[hepplerj]

Here are the current views I've defined.

Populated places:

CREATE VIEW export_relcensus.populated_places
AS SELECT
	place_id,
	place,
	county,
	state,
	county_ahcb,
    population_1920,
	population_1930,
	elevation,
	lat,
	lon
FROM
	popplaces_1926
ORDER BY state, county;

City membership:

CREATE VIEW export_relcensus.city_membership
AS SELECT
	year,
	city,
	state,
	denomination,
	denomination_id, 
	churches,
	members_total,
	members_over_13, 
	members_under_13,
	members_male,
	members_female,
	id
FROM
	relcensus.membership_city
ORDER BY year, denomination;

Denominations:

CREATE VIEW export_relcensus.denominations
AS SELECT 
	year, 
	denomination_id, 
	name, 
	short_name, 
	family_census, 
	family_arda, 
	family_relec, 
	id
FROM relcensus.denominations
ORDER BY year, name;

[hepplerj]

@lmullen @qtrinh2 Happy to receive any feedback you might have.

Lincoln, for any dataset that had a year I used the year + some meaningful data point as our ORDER. For the populated places, it made sense to me to have the data sorted by state then county (thinking as a user, if I were interested in a specific state I'd want that alphabetically sorted so I could easily find it, then find my alphabetically sorted county I was interested in examining). We can modify these sorts if desired.

[hepplerj]

Quick followup: @qtrinh2 and I discussed renaming the schema from export_relcensus to exports, so we can handle any additional projects that might use a similar workflow.

Instead, the view will be the project name and something descriptive. For example: exports.relcensus_denominations.

CREATE VIEW exports.relcensus_denominations
AS SELECT 
	year, 
	denomination_id, 
	name, 
	short_name, 
	family_census, 
	family_arda, 
	family_relec, 
	id
FROM relcensus.denominations
ORDER BY year, name;

[hepplerj]

Placing this discussion here so we can keep things organized while I prep the Church of the Living God, “The Pillar and Ground of Truth” dataset. To aggregate that data, here's the SQL that can pull everything together.

SELECT m.year, m.denomination, 
    c.city, c.state,
    1::integer AS denominations,
    m.churches, m.members_total,
    p.pop_est_1926,
    ST_X(c.geometry) AS lon, ST_Y(c.geometry) AS lat
FROM relcensus.membership_city m
LEFT JOIN relcensus.cities_25K c ON m.city = c.city AND m.state = c.state
LEFT JOIN relcensus.popplaces_1926 p ON c.place_id = p.place_id
WHERE denomination = 'Church of the Living God, “The Pillar and Ground of Truth”'
ORDER BY state, city;

This will give us data structured like this excerpt:

We can chat about this at our next team meeting tomorrow. As a side note, this should be a fairly reusable query so we can switch out the denomination for any other denomination we'd like.

[hepplerj]

We can also save this as an export, I'd just have to tweak a couple things.