Skip to main content
Version: v0.20

Customize

Summary

This plugin provides users the ability to:

  • Add/delete columns in domain layer tables
  • Insert values to certain columns with data extracted from some raw layer tables
  • Import data from CSV files(only issues and issue_commits two tables are supported)

NOTE: The names of columns added via this plugin must start with the prefix x_

For now, only the following five types were supported:

  • varchar(255)
  • text
  • bigint
  • float
  • timestamp
  • array

Sample Request

Trigger Data Extraction

To extract data, switch to Advanced Mode on the first step of creating a Blueprint and paste a JSON config as the following:

The example below demonstrates how to extract status name from the table _raw_jira_api_issues:

  1. For non-array types: Extract the status name from the _raw_jira_api_issues table and assign it to the x_test column in the issues table.
  2. For array types: Extract the status name from the _raw_jira_api_issues table, and create a new issue_custom_array_fields table containing issue_id, field_id, and value columns. This table has a one-to-many relationship with the issues table. issue_id is the id corresponding to the issue, x_test corresponds to the field_id column, and the value of x_test corresponds to the value column.

We leverage the package https://github.com/tidwall/gjson to extract value from the JSON. For the extraction syntax, please refer to this docs

  • table: domain layer table name
  • rawDataTable: raw layer table, from which we extract values by json path
  • rawDataParams: the filter to select records from the raw layer table (The value should be a string not an object)
  • mapping: the extraction rule; the key is the extension field name; the value is json path
[
[
{
"plugin":"customize",
"options":{
"transformationRules":[
{
"table":"issues",
"rawDataTable":"_raw_jira_api_issues",
"rawDataParams":"{\"ConnectionId\":1,\"BoardId\":8}",
"mapping":{
"x_test":"fields.status.name"
}
}
]
}
}
]
]

You can also trigger data extraction by making a POST request to /pipelines.

curl 'http://localhost:8080/pipelines' \
--header 'Content-Type: application/json' \
--data-raw '
{
"name": "extract fields",
"plan": [
[
{
"plugin": "customize",
"options": {
"transformationRules": [
{
"table": "issues",
"rawDataTable": "_raw_jira_api_issues",
"rawDataParams": "{\"ConnectionId\":1,\"BoardId\":8}",
"mapping": {
"x_test": "fields.status.name"
}
}
]
}
}
]
]
}
'

List Columns

Get all columns of the table issues

GET /plugins/customize/issues/fields

NOTE some fields are omitted in the following example response

[
{
"columnName": "id",
"displayName": "",
"dataType": "varchar(255)",
"description": ""
},
{
"columnName": "created_at",
"displayName": "",
"dataType": "datetime(3)",
"description": ""
},
{
"columnName": "x_time",
"displayName": "time",
"dataType": "timestamp",
"description": "test for time"
},
{
"columnName": "x_int",
"displayName": "bigint",
"dataType": "bigint",
"description": "test for int"
},
{
"columnName": "x_float",
"displayName": "float",
"dataType": "float",
"description": "test for float"
},
{
"columnName": "x_text",
"displayName": "text",
"dataType": "text",
"description": "test for text"
},
{
"columnName": "x_varchar",
"displayName": "varchar",
"dataType": "varchar(255)",
"description": "test for varchar"
}
]

Create a Customized Column

Create a new column x_abc with datatype varchar(255) for the table issues.

The value of columnName must start with x_ and consist of no more than 50 alphanumerics and underscores. The value of field dataType must be one of the following 5 types:

  • varchar(255)
  • text
  • bigint
  • float
  • timestamp

POST /plugins/customize/issues/fields

{
"columnName": "x_abc",
"displayName": "ABC",
"dataType": "varchar(255)",
"description": "test field"
}

Drop A Column

Drop the column x_text of the table issues

DELETE /plugins/customize/issues/fields/x_test

Upload issues.csv file

POST /plugins/customize/csvfiles/issues.csv

The HTTP Content-Type must be multipart/form-data, and the form should have three fields:

  • file: The CSV file
  • boardId: It will be written to the id field of the boards table, the board_id field of board_issues, and the _raw_data_params field of issues
  • boardName: It will be written to the name field of the boards table

Upload a CSV file and import it to the issues table via this API. There should be no extra fields in the file except the labels field, and if the field value is NULL, it should be NULL in the CSV instead of the empty string. DevLake will parse the CSV file and store it in the issues table, where the labels are stored in the issue_labels table. If the boardId does not appear, a new record will be created in the boards table. The board_issues table will be updated at the same time as the import. The following is an issues.CSV file sample:

id_raw_data_paramsurlicon_urlissue_keytitledescriptionepic_keytypestatusoriginal_statusstory_pointresolution_datecreated_dateupdated_dateparent_issue_idpriorityoriginal_estimate_minutestime_spent_minutestime_remaining_minutescreator_idcreator_nameassignee_idassignee_nameseveritycomponentlead_time_minutesoriginal_projectoriginal_typex_intx_timex_varcharx_floatlabels
bitbucket:BitbucketIssue:1:1board789https://api.bitbucket.org/2.0/repositories/thenicetgp/lake/issues/11issue testbitbucket issues test for devlakeissueTODOnew0NULL2022-07-17 07:15:55.959+00:002022-07-17 09:11:42.656+00:00major000bitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cftgpbitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cftgpNULLNULLNULL102022-09-15 15:27:56world8NULL
bitbucket:BitbucketIssue:1:10board789https://api.bitbucket.org/2.0/repositories/thenicetgp/lake/issues/1010issue test007issue test007issueTODOnew0NULL2022-08-12 13:43:00.783+00:002022-08-12 13:43:00.783+00:00trivial000bitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cftgpbitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cftgpNULLNULLNULL302022-09-15 15:27:56abc2456790hello worlds
bitbucket:BitbucketIssue:1:13board789https://api.bitbucket.org/2.0/repositories/thenicetgp/lake/issues/1313issue test010issue test010issueTODOnew0NULL2022-08-12 13:44:46.508+00:002022-08-12 13:44:46.508+00:00critical000bitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cftgpNULLNULLNULL12022-09-15 15:27:56NULL0.00014NULL
bitbucket:BitbucketIssue:1:14board789https://api.bitbucket.org/2.0/repositories/thenicetgp/lake/issues/1414issue test011issue test011issueTODOnew0NULL2022-08-12 13:45:12.810+00:002022-08-12 13:45:12.810+00:00blocker000bitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cftgpbitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cftgpNULLNULLNULL415345684643512022-09-15 15:27:56NULLNULLlabel1,label2,label3

Upload issue_commits.csv file

POST /plugins/customize/csvfiles/issue_commits.csv

The Content-Type should be multipart/form-data, and the form should have two fields:

  • file: The CSV file
  • boardId: It will be written to the _raw_data_params field of issue_commits

The following is an issue_commits.CSV file sample:

issue_idcommit_sha
jira:JiraIssue:1:100638748a066cbaf67b15e86f2c636f9931347e987cf
jira:JiraIssue:1:10064e6bde456807818c5c78d7b265964d6d48b653af6
jira:JiraIssue:1:100658f91020bcf684c6ad07adfafa3d8a2f826686c42
jira:JiraIssue:1:100660dfe2e9ed88ad4e27f825d9b67d4d56ac983c5ef
jira:JiraIssue:1:1014507aa2ebed68e286dc51a7e0082031196a6135f74
jira:JiraIssue:1:10145d70d6687e06304d9b6e0cb32b3f8c0f0928400f7
jira:JiraIssue:1:10159d28785ff09229ac9e3c6734f0c97466ab00eb4da
jira:JiraIssue:1:102020ab12c4d4064003602edceed900d1456b6209894
jira:JiraIssue:1:10203980e9fe7bc3e22a0409f7241a024eaf9c53680dd