Describe how to join raw KoboToolbox values with labels stored in `__labels` table

[rudokemper]

Following up on ConservationMetrics/gc-scripts-hub#139.

The KoboToolbox API returns raw data values e.g. "n_o" instead of "não", which is a problem for many languages, and means that we currently show "n_o" instead of "não" on any frontend (like Superset), unless we do a manual SQL rewrite -- neither of which is desirable. See ConservationMetrics/gc-scripts-hub#29 for additional context.

To address this, in ConservationMetrics/gc-scripts-hub#96 we began to store all of the human-legible labels returned by KoboToolbox (via a different endpoint than the raw data itself) in a __labels lookup table, with the intent of being able to reconstruct labels, and indeed also translations, on a given frontend, e.g.

• n_o -> "não" (Portuguese)
• n_o -> "uwa" (Tyrio)

To see how this works, see the KoboToolbox connector documentation in gc-scripts-hub here.

However, we have yet to describe how to actually do this; in particular, Superset charts really could benefit from this. In the past we've even written some custom SQL to rewrite raw data values back to their human-legible equivalent!

Let's produce a page for Guardian Connector Toolkit -> Apache Superset that comprehensively describes how to do this.

[nicopace]

Value translation

I've tested an approach to generalize the need for the translations using the __labels table.

Here is an example with our demo database:

-- Step 1: Create the function.
CREATE OR REPLACE FUNCTION get_translation(
    value_to_translate TEXT,
    lang TEXT
)
RETURNS TEXT AS $$
DECLARE
    translated_value TEXT;
BEGIN
    SELECT
        label
    INTO
        translated_value
    FROM
        responses__labels
    WHERE
        name = value_to_translate
        AND language = lang
        AND type = 'choices'
    LIMIT 1;

    RETURN COALESCE(translated_value, value_to_translate);
END;
$$ LANGUAGE plpgsql;

And then you have this function available to use within your queries, like this:

SELECT
  r._id,
  r._submission_time,
  r.community,
  -- Call the function for each column you need to translate.
  get_translation(r.traditional_harvesting, 'es') AS traditional_harvesting,
  get_translation(r.data_source, 'es') AS data_source,
  get_translation(r.availability_resources, 'es') AS availability_resources,
  get_translation(r.important_resources, 'es') AS important_resources,
  get_translation(r.threats, 'es') AS threats
  -- ... and so on for other columns
FROM
  responses AS r;

So, the manual process is to create one entry per column that needs to be translated, like this get_translation(r.{name_of_column}, '{lang}') as {name of column}.

[nicopace]

For the column names, it is more challenging...

A few options at hand:

1. within SQL it is recommended to build views, but it is a handcrafted SQL per form import.
2. Alternatively, we can create a windmill script that runs after importing the kobotoolbox responses, and use it to create virtual tables with the translations in place...
3. When we run the windmill script, we can use postgresql variable substitution functionality, like this:

-- In your psql session
\set threats_alias 'Amenazas'
\set community_alias 'Comunidad'

-- Then in your query
SELECT
  threats AS :"threats_alias",
  community AS :"community_alias"
FROM responses;

I

[nicopace]

Need to understand how this interacts with this Superset functionality: apache/superset#13442

[rudokemper]

Thanks @nicopace.

Since it is possible to create virtual tables in Superset, I wonder if you could try your SQL code there, to see if it works for both rows and columns? (And because the most urgent need for this feature is in Superset.)

Also, would love to get @IamJeffG thoughts on this.

[nicopace]

Value translation

I've tested an approach to generalize the need for the translations using the __labels table.

Here is an example with our demo database:

-- Step 1: Create the function.

-- Step 1: Create the function.
CREATE OR REPLACE FUNCTION get_translation(
    value_to_translate TEXT,
    lang TEXT
)
RETURNS TEXT AS $$
DECLARE
    translated_value TEXT;
BEGIN
    SELECT
        label
    INTO
        translated_value
    FROM
        responses__labels
    WHERE
        name = value_to_translate
        AND language = lang
        AND type = 'choices'
    LIMIT 1;

    RETURN COALESCE(translated_value, value_to_translate);
END;
$$ LANGUAGE plpgsql;

This is not possible within Superset as it is a write action.
Superset says:

This database does not allow for DDL/DML, and the query could not be parsed to confirm it is a read-only query. 
Please contact your administrator for more assistance.

[IamJeffG]

There may be promise in this FUNCTION get_translation idea. As @nicopace discovered you will need to create it against the database directly (eventually we should do this from the same windmill script that populates __labels) -- not from Superset.

I want to make sure we know that this can be used in practice: So

• A question I have is can superset queries (or superset views) call a postgres function?
• @nicopace mentioned Postgres VIEWs, which I am also a huge fan of. I am 95% sure that you can define -- in postgresql (not in superset) -- a view that calls our custom function: CREATE VIEW responses_en_espanol AS SELECT id, get_translation(name, 'es') AS name FROM responses, and then you can have a Superset chart query that VIEW. But is the plan then to create a separate VIEW for each language?

One reason I am not a fan of using a FUNCTION is because of performance: called over hundreds of rows in responses and on many columns, you might be calling get_translation thousands of times per query. If we are already going to create new translated VIEWs per language, then I suggest those VIEWs are the place to join against __labels: this will be significantly more efficient. (You could still define get_translation for convenience in one-off analysis but I don't know that it has a place in the superset use case). Simple example of the VIEW joining to translation table:

CREATE OR REPLACE VIEW responses_translated_fr AS
SELECT
    r.id,
    COALESCE(l.label, r.name) AS name_fr
FROM
    responses r
LEFT JOIN
    responses__labels l
    ON l.name = r.name
    AND l.language = 'fr'
    AND l.type = 'choices';  -- btw, what is "type" and how's it used in translation?

It may not be so easy to translate dozens of columns: my mind goes to having to repeat the join for each column (ON l.othercol=r.othercol).

• We could probably think of clever ways to avoid that but it will likely have trade-offs.
• In fact we once went down this path before: https://github.com/ConservationMetrics/arunava/pull/24 -- the details are not top-of-mind to me, I think that repo might have used a wide format for the translation table and/or a long format for the responses table.

You could always define the per-language VIEWs using get_translation (instead of a JOIN) and we re-visit when performance gets noticeably bad.

---

I also have another around how to define these per-language VIEWs in the first place (or more correctly: how we can avoid having to enumerate all of them) but let's figure out the questions above first.

[nicopace]

btw, what is "type" and how's it used in translation?

You can translate two things:

1. column names
2. multiple choice options

Type differenciates between these two.

[nicopace]

coming back to this:

• i do think we need to pay attention to performance
• we should factor in that data comes from somewhere else and then we process it in order to be here through windmill, so more processing could also be the way to go
• queries rely on the columns to be named the way they are, and if we do views that change the name of columns, then we are stuck in a tricky situation where we need to rewrite our queries

Maybe we can create a static function to do this translation and use that function instead of having this in a table... I think they could fit right in...

Will keep thinking about it, and researching about this too.

[nicopace]

Superset has been thinking about this, and there is a lot of knowledge that will try to bring from here apache/superset#13442 to this space.

A conclusion form our previous conversations is that although it is possible to use SQL queries to translate values and columns, values are translatable in practical terms, but column names are not practical, as they require to rewrite the queries for each of the languages supported.

I am researching within the SuperSet community what is that they recommend.

[nicopace]

A comment in relation to the translations in database approach:

an in-database solution, allowing to store the strings and translation within the metadata database. Personally I'm not big on this solution as it adds a lot of complexity to the app and has perf/complexity-creep concerns.

[rudokemper]

If you haven't already, Nico, joining the Superset Slack could be useful. I've found the community to be pretty helpful in the past, even if just to confirm a "no this isn't possible" answer.

[nicopace]

Thanks for the suggestion @rudokemper .
Haven't yet gotten to the Slack group yet.
Will make sure to get there and validate the exploration with them before we choose a direction, or even if I get blocked or am in a crossroad, will ask them.

Right now I am learning about the jinja templating that Superset brings to the table, and how this could be leveraged together with views...

I am thinking that we could combine postgresql views to do the translation of the values, and jinja templates to translate column values.

Now validating assumptions...