Mysql

Description

Create an API to allow Mysql to be accessible via REST.

UI

Access the DataTrucker URL via a browser

Create a credentials

1. Go to Credentials in the sidebar > Mysql Credentials
2. Scroll down to Credential Management
3. Fill the form to create the connection
   1. Credential name – an arbitrary value to identify the credential object
   2. The hostname of the Mysql. Note, make sure the API server(not the UI) has access into Mysql
   3. Username of Mysql,
      1. Limit access to this user to the specific API. Please reach out to a DBA to provide specific grant access to this user
   4. The password of the Mysql User
   5. Database Name
   6. Port of the Database
   7. Min /Max Pool for connection pooling.

Make an API

1. Go to Database API in the Sidebar > Mysql API’s
2. Fill the form to create the API
   1. Resource Name: an arbitrary name to identify the resource
   2. The Credential linked to the resources
   3. Method: The type of REST Call
   4. Timeout: To ensure query times out after a set period in time
   5. Script: The SQL Query
      1. The SQL query can have parameters identified by ‘[[]]’ Example ‘[[id]]’ [ single quotes recommended instead of double quotes ]”
      2. Parameters are case sensitive in some cases
   6. Validation Regex of Input values, input sanitization before querying the database
      1. Examples provided in the UI when you try to create a new API

Query the resource you created

URL: /api/v1/jobs/<resource name>TYPE: <method defined>HEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "<input_key>": "<value>",        "<input_key>": "<value>",        "<input_key>": "<value>",        "<input_key>": "<value>"}
Response: 200 OK{   jsondata....}

As a CRD in Openshift / Kubernetes

For credentials use the the API below to the management end point

---apiVersion: datatrucker.datatrucker.io/v1kind: DatatruckerFlowmetadata:  name: datatruckerflow-samplespec:  Resources:    requests:      memory: "256Mi"      cpu: "250m"    limits:      memory: "256Mi"      cpu: "500m"  JobDefinitions:  - resourcename: mysql1    name: mysql1    tenant: Admin    type: DB-Mysql    credentialname: mysqldata1    restmethod: POST    script: " select CURDATE() as 'current_date' ,id, user,'[[dbname]]' from information_schema.processlist; "    job_timeout: 4354    validations:      type: object      properties:        dbname:          type: string          pattern: "^[a-z0-9]*$"          maxLength: 8  Keys:    configmap: placeholder  Scripts:    configmap: placeholder  Type: Job  DatatruckerConfig: datatruckerconfig-sample  Replicas: 1  API:    name: api    Image:      repository: docker.io      imageName: datatruckerio/datatrucker-api      tagName: latest  

API

Create a credential via REST

URL: /api/v1/credentials/TYPE: POSTHEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "credentialname": "mysqldata1",        "type": "DB-Mysql",        "hostname": "localhost",        "username": "sa",        "password": "password",        "database": "primary",        "port": 3303,        "minpool": 100,        "maxpool": 200}
Response: 201 OK

Create the API via REST

URL: /api/v1/resourcesTYPE: POSTHEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "resourcename": "mysql1",        "type": "DB-Mysql",        "credentialname": "mysqldata1",        "restmethod": "POST",        "script": " select CURDATE() as 'current_date' ,id, user,'[[dbname]]' from information_schema.processlist; ",        "job_timeout": 4354,        "validations": {          "type": "object",          "properties": {            "dbname": {              "type": "string",              "pattern": "^[a-z0-9]*$",              "maxLength": 8            }          }        }      }

Response: 201 OK