OpenFn Training

​

April 2021

ConSoSci Open Function Group Version 2.0

Day 6 Agenda

​

1. Recap: Integration Design & Mapping
2. Job Configuration Practice (Jobs, Triggers, Creds)
3. Job Writing 101

Recap: �Custom Integration Design & �Data Element Mapping

​

Given...

​

(1) some data from Kobo �→ Message,

​

(2) your careful instructions → Job,

​

(3) and a way to connect to a destination �→ Adaptor + Credential

​

...the openfn job_runner can get to work.

Given...

​

(1) some data from Kobo �→ state.json,

​

(2) your careful instructions → job expression.js,

​

(3) and a way to connect to a destination �→ adaptor + credential

​

...the openfn job_runner can get to work.

Jobs define operations in your destination database...

insert('KoboData', {� AnswerId: dataValue('_id'),

Column: dataValue('kobo_question'),

SurveyDate: dataValue(‘submission_time’),

...

});

upsert('WCSPROGRAMS_KoboBnsAnswer', 'AnswerId', {� AnswerId: dataValue('_id'), //external Id for upsert

column: dataValue('kobo_question'),� column: state.data.kobo_question, //same as above

LastUpdate: new Date().toISOString(),

Participant: dataValue('participant'),

Landscape: dataValue('landscape'),

...

});

​

Jobs map data elements, referencing JSON keys...

Standard mapping syntax…��DatabaseColumn: dataValue(‘path.kobo_question’),

Jobs can be extended using JavaScript to clean data...

ConSoSci Open Function Group Version 2.0

You don’t need to be a developer to design jobs…��If you can clearly define “rules” for data element mapping, you can get to work!

​

ConSoSci Open Function Group Version 2.0

Step 1: Map your data flows

1. Define your input(s) → What Kobo form are used to collect data? �How many forms? Are there different form versions?
2. Define your output(s)→ Where should the data be stored? In what format? What are your analysis requirements?

​

ConSoSci Open Function Group Version 2.0

Step 2: Map your data elements

• Export the metadata of your Kobo form (input) & destination DB (output)
• Paste the metadata into an Excel spreadsheet → see Mapping template
• Map data elements & define rules for data cleaning and transformation
1. How should the data collected be translated into your destination system’s data model?
2. Does your destination system have data input & validation requirements?

​

ConSoSci Open Function Group Version 2.0

Mapping Sharks & Rays...

​

Example DB

Kobo form

ConSoSci Open Function Group Version 2.0

How to export Kobo metadata...

ConSoSci Open Function Group Version 2.0

Step 3: Define your database operations

​

Adaptors provide access to helpers for quicker job-writing! �language-postgres examples...

• Insert(...), InsertMany(...)
• Update(...), UpdateMany(...)
• Upsert(...), UpsertMany(...) → update if record exists or insert if it doesn’t; references an external Id

​

ConSoSci Open Function Group Version 2.0

Allow yourself to fail. �

It’s best practice to plan for scenarios where your jobs will fail and you will need to re-try the jobs to reprocess the data.

​

Use unique identifiers to build idempotent automation.

�For an operation to be idempotent means that it can be repeated time and time again without producing an unintended result (i.e., inserting duplicate data).

​

ConSoSci Open Function Group Version 2.0

More on Unique Identifiers... �

1. Kobo-generated identifiers
• "formId": "adiNTJXFtpKEDGGZFMUtgQ" → unique form instance
• "_id": 85252496 → form submission, unique across Kobo server
• "_uuid" → changes every time submission is cleaned; do not use �
2. Custom Ids - build your own
• "GeneratedUuid":"..."
• E.g., concatenate _id + boattype ⇒ "boat_id":"dhow-85252496"

​

Job configuration practice...

​

​

Confidential Open Function Group Version 2.0

Recap & configuration practice...

​

1. Jobs
2. Triggers
3. Adaptors
4. Credentials

​

Job writing...

​

​

Confidential Open Function Group Version 2.0

Job writing...

​

• About the Github integration configuration
• Job studio → writing expressions
• Intro to Adaptors and helper functions → upsert(...), each(...)
• Data transformations → alterState(...)

​

Confidential Open Function Group Version 2.0

Your turn: Let’s write jobs!

​

Example DB

state.json

Confidential Open Function Group Version 2.0

Sharks and Rays

​

• state: https://www.openfn.org/projects/p5w6xn/messages/mdbwqazv�https://github.com/OpenFn/consocsci-training/blob/main/sampleData/state_sharksAndRays.json�
• Job draft:
• https://github.com/OpenFn/consocsci-training/blob/main/sharksAndRays.js

​

Job writing...

​

• Job configuration
• Job studio → writing expressions
• Adaptors and helper functions → upsert(...), each(...)
• Data transformations → alterState(...)

​

Writing expressions in the Job studio...

​

Confidential Open Function Group Version 2.0

Testing...

​

• Find your state → sample Message
• Run the job and monitor output in Activity History
• Consider a testing plan to cover a variety of key scenarios

​

Confidential Open Function Group Version 2.0

Click into the run to review the logs...

Exit codes...�0. Success�1. Error �2-5. above (contact OpenFn support)

Confidential Open Function Group Version 2.0

Read the logs to understand the error...

HTTP Status Code: 400s → client errors (Double check your authentication & request)� 500s → server errors (Check Kobo’s status & your query parameters)

The request may contain bad syntax and cannot be fulfilled, record conflict, authentication error, or Kobo server-side issue, etc.

HTTP Status Code� from Kobo

Confidential Open Function Group Version 2.0

Read the logs to understand the error...

Database errors - “RequestError”, “Conversion failed”

Review the Message data (input) and Job mappings. To fix, you may need to update the DB configuration or add transformation rules to the job.

Sql DB errors…��What does this example mean?

Confidential Open Function Group Version 2.0

Read the logs to understand the error...

TypeError- Check your job

There is something wrong with how your job is written… this might be as small as a syntax error, or perhaps your job wasn’t written to handle certain data input scenarios.