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
andissue_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
:
- For non-array types: Extract the status name from the
_raw_jira_api_issues
table and assign it to thex_test
column in the issues table. - For array types: Extract the status name from the
_raw_jira_api_issues
table, and create a new issue_custom_array_fields table containingissue_id
,field_id
, andvalue
columns. This table has a one-to-many relationship with theissues
table.issue_id
is the id corresponding to the issue,x_test
corresponds to thefield_id
column, and the value ofx_test
corresponds to thevalue
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 namerawDataTable
: raw layer table, from which we extract values by json pathrawDataParams
: 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 fileboardId
: It will be written to theid
field of theboards
table, theboard_id
field ofboard_issues
, and the_raw_data_params
field ofissues
boardName
: It will be written to thename
field of theboards
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_params | url | icon_url | issue_key | title | description | epic_key | type | status | original_status | story_point | resolution_date | created_date | updated_date | parent_issue_id | priority | original_estimate_minutes | time_spent_minutes | time_remaining_minutes | creator_id | creator_name | assignee_id | assignee_name | severity | component | lead_time_minutes | original_project | original_type | x_int | x_time | x_varchar | x_float | labels |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
bitbucket:BitbucketIssue:1:1 | board789 | https://api.bitbucket.org/2.0/repositories/thenicetgp/lake/issues/1 | 1 | issue test | bitbucket issues test for devlake | issue | TODO | new | 0 | NULL | 2022-07-17 07:15:55.959+00:00 | 2022-07-17 09:11:42.656+00:00 | major | 0 | 0 | 0 | bitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cf | tgp | bitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cf | tgp | NULL | NULL | NULL | 10 | 2022-09-15 15:27:56 | world | 8 | NULL | |||||
bitbucket:BitbucketIssue:1:10 | board789 | https://api.bitbucket.org/2.0/repositories/thenicetgp/lake/issues/10 | 10 | issue test007 | issue test007 | issue | TODO | new | 0 | NULL | 2022-08-12 13:43:00.783+00:00 | 2022-08-12 13:43:00.783+00:00 | trivial | 0 | 0 | 0 | bitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cf | tgp | bitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cf | tgp | NULL | NULL | NULL | 30 | 2022-09-15 15:27:56 | abc | 2456790 | hello worlds | |||||
bitbucket:BitbucketIssue:1:13 | board789 | https://api.bitbucket.org/2.0/repositories/thenicetgp/lake/issues/13 | 13 | issue test010 | issue test010 | issue | TODO | new | 0 | NULL | 2022-08-12 13:44:46.508+00:00 | 2022-08-12 13:44:46.508+00:00 | critical | 0 | 0 | 0 | bitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cf | tgp | NULL | NULL | NULL | 1 | 2022-09-15 15:27:56 | NULL | 0.00014 | NULL | |||||||
bitbucket:BitbucketIssue:1:14 | board789 | https://api.bitbucket.org/2.0/repositories/thenicetgp/lake/issues/14 | 14 | issue test011 | issue test011 | issue | TODO | new | 0 | NULL | 2022-08-12 13:45:12.810+00:00 | 2022-08-12 13:45:12.810+00:00 | blocker | 0 | 0 | 0 | bitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cf | tgp | bitbucket:BitbucketAccount:1:62abf394192edb006fa0e8cf | tgp | NULL | NULL | NULL | 41534568464351 | 2022-09-15 15:27:56 | NULL | NULL | label1,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 fileboardId
: It will be written to the_raw_data_params
field ofissue_commits
The following is an issue_commits.CSV file sample:
issue_id | commit_sha |
---|---|
jira:JiraIssue:1:10063 | 8748a066cbaf67b15e86f2c636f9931347e987cf |
jira:JiraIssue:1:10064 | e6bde456807818c5c78d7b265964d6d48b653af6 |
jira:JiraIssue:1:10065 | 8f91020bcf684c6ad07adfafa3d8a2f826686c42 |
jira:JiraIssue:1:10066 | 0dfe2e9ed88ad4e27f825d9b67d4d56ac983c5ef |
jira:JiraIssue:1:10145 | 07aa2ebed68e286dc51a7e0082031196a6135f74 |
jira:JiraIssue:1:10145 | d70d6687e06304d9b6e0cb32b3f8c0f0928400f7 |
jira:JiraIssue:1:10159 | d28785ff09229ac9e3c6734f0c97466ab00eb4da |
jira:JiraIssue:1:10202 | 0ab12c4d4064003602edceed900d1456b6209894 |
jira:JiraIssue:1:10203 | 980e9fe7bc3e22a0409f7241a024eaf9c53680dd |