跳至主要内容
版本:v1

Restful API V1

Chinese Version Only

For Canner Enterprise v1.x.x Documentation, we only provide Chinese version available.

驗證

要使用 Restful API ,必須先取得 Personal Access Token,並附在 Request headers

headers: {
Authorization: `Token <Your Personal Access Token>`
}

Cache Control

使用 POST /query 執行 Query 時,可以使用下列兩個參數在 body 中控制 Query 的 Cache

  1. cacheTTL: 此值為秒數,代表最長可容忍 Query Cache 多久,例如設定為 3600 (秒),代表若 Cache 的 Query 結果超過 3600 秒,就會重新執行 Query,抓取新的資料.
  2. cacheRefresh: Boolean 值,"true" 或是 "false",代表是否要無視 Cache,重新執行 Query

若希望使用 Cache 機制,兩個參數皆要設置

API Endpoint

API endpoint 即是網頁的網址到 /web 的部分,再加上 /api/v1,例如:https://web.default.myname.apps.cannerdata.com/web/api/v1

Params & Input

在這份文件中, request 挾帶的資料分為 3 種

  • Params: 例如 /resource/:id 中的 id
  • Query String: 例如 /resource?workspaceId=<workspaceId> 中的 workspaceId
  • Input: 在 Request Body 中的資料,統一為 JSON Object

Workspace ID

在工作區範疇之下的操作,例如 Saved SQL 以及 Query,會需要在 params 或是 request body 當中攜帶 workspaceId,這個 id 可以在工作區網址中找到。

例如: https://web.default.myname.apps.cannerdata.com/workspaces/444e8753-a4c0-4875-bdc0-834c79061d56,其中的 444e8753-a4c0-4875-bdc0-834c79061d56 即是 workspaceId

Errors

常見的 Error 有以下幾種

  • status: 400,使用者給予不正確的參數格式,例如: ?limit=some-stringlimit 必須為 number
  • status: 401,錯誤的驗證資訊以致於沒有權限取得改資源。例如沒有夾帶 Authorization header
  • status: 404,找不到給定的資源。

Service

GET /service/:service

取得服務狀態。

Params

namedescription
service服務名稱,例如: SQL_ENGINE

Response

  • status: 200
    {
    status: string;
    lastUsed: iso string;
    id: string;
    service: string;
    maxIdleTime: number;
    }

PUT /service/:service

更新服務狀態,例如:喚醒特定服務。

Params

namedescription
service服務名稱,例如: SQL_ENGINE

Input

namedescription
statetrue, false,開啟或關閉服務。

Response

  • status: 200

Saved SQL

GET /sqls

Query String

namedescription
workspaceId-

Response

  • status: 200
    [
    {
    id: string;
    title: string;
    description?: string;
    sql: string;
    workspaceId: string;
    accountId: string;
    }
    ]

GET /sql/:id

Params

namedescription
idSaved SQL 的 ID

Query String

namedescription
workspaceId-

Response

  • status: 200
    {
    id: string;
    stats: {
    state: string;
    queued: boolean;
    scheduled: boolean;
    nodes: number,
    totalSplits: number,
    queuedSplits: number,
    runningSplits: number,
    completedSplits: number,
    cpuTimeMillis: number,
    wallTimeMillis: number,
    queuedTimeMillis: number,
    elapsedTimeMillis: number,
    processedRows: number,
    processedBytes: number,
    peakMemoryBytes: number,
    spilledBytes: number,
    };
    }

POST /sql

儲存一個新的 SQL 程式碼,以便之後可以重複執行。

Input

namedescription
workspaceId-
sqlSQL 程式碼
description對於此儲存的 SQL 描述
title儲存的 SQL 所顯示的標題

Response

  • status: 200
    {
    id: string;
    title: string;
    description?: string;
    sql: string;
    workspaceId: string;
    }

POST /sql/:id

使用 Saved SQL,會回傳一個 Query Object

Params

namedescription
idSaved SQL 的 ID

Input

namedescription
workspaceId-

Response

  • status: 200
    {
    id: string;
    statementId: string | null;
    accountId?: string | null;
    status?: 'QUEUED' | 'PLANNING' | 'RUNNING' | 'FINISHED' | 'FAILED';
    startedAt?: string;
    sql?: string;
    duration?: number;
    workspaceId: string;
    workspaceSqlName?: string;
    location?: string;
    error?: Record<string, any>;
    userId?: string;
    userType?: string;
    rowCount?: number;
    fromCache?: boolean;
    source?: Source;
    columns?: Array<Record<string, any>>;
    queryStats?: Record<string, any>;
    outputStage?: Record<string, any>;
    dataFormat?: DataFormat;
    }

Query

GET /queries

Query String

namedescription
workspaceId-
limit返回資料數量
offset略過資料數量

Response

  • status: 200
    {
    totalCount:number
    result: Array<{
    id: string;
    statementId: string | null;
    accountId?: string | null;
    status?: 'QUEUED' | 'PLANNING' | 'RUNNING' | 'FINISHED' | 'FAILED';
    startedAt?: string;
    sql?: string;
    duration?: number;
    workspaceId: string;
    workspaceSqlName?: string;
    location?: string;
    error?: Record<string, any>;
    userId?: string;
    userType?: string;
    rowCount?: number;
    fromCache?: boolean;
    source?: Source;
    columns?: Array<Record<string, any>>;
    queryStats?: Record<string, any>;
    outputStage?: Record<string, any>;
    dataFormat?: DataFormat;
    }>
    }

GET /query/:id

Query String

namedescription
idQuery 的 ID

Params

namedescription
workspaceId-

Response

  • status: 200
    {
    id: string;
    statementId: string | null;
    accountId?: string | null;
    status?: 'QUEUED' | 'PLANNING' | 'RUNNING' | 'FINISHED' | 'FAILED';
    startedAt?: string;
    sql?: string;
    duration?: number;
    workspaceId: string;
    workspaceSqlName?: string;
    location?: string;
    error?: Record<string, any>;
    userId?: string;
    userType?: string;
    rowCount?: number;
    fromCache?: boolean;
    source?: Source;
    columns?: Array<Record<string, any>>;
    queryStats?: Record<string, any>;
    outputStage?: Record<string, any>;
    dataFormat?: DataFormat;
    }

GET /query/:id/result

取得 Query 的執行結果

Params

namedescription
idQuery 的 ID

Query String

namedescription
workspaceId-
limit返回資料數量
offset略過資料數量

Response

  • status: 200
    {
    result: any;
    columns: Record<string, any>[];
    rowCount: number;
    }

POST /query

執行 SQL

Input

namedescriptiontype
workspaceId-String
sqlSQL 程式碼String
cacheRefresh是否重新刷新 CacheBoolean
cacheTTLCache Time-to-live (Cache 保留時間)Number

Response

  • status: 200

    {
    id: string;
    statementId: string | null;
    accountId?: string | null;
    status?: 'QUEUED' | 'PLANNING' | 'RUNNING' | 'FINISHED' | 'FAILED';
    startedAt?: string;
    sql?: string;
    duration?: number;
    workspaceId: string;
    workspaceSqlName?: string;
    location?: string;
    error?: Record<string, any>;
    userId?: string;
    userType?: string;
    rowCount?: number;
    fromCache?: boolean;
    source?: Source;
    columns?: Array<Record<string, any>>;
    queryStats?: Record<string, any>;
    outputStage?: Record<string, any>;
    dataFormat?: DataFormat;
    }

執行範例

import axios from 'axios';
import pRetry from 'p-retry';
import delay from 'delay';

const getData = async ({refresh = false, ttl, sql}) => {
const workspaceId = "9d5c99c3-71bd-4090-a5db-xxxxx";
const endpoint = "https://staging-xx-xxx.dev.apps.cannerdata.com/web/api/v1";
const token = "Y2xpZW50XzM3YzNiNDxxxx";

const client = axios.create({
baseURL: endpoint,
headers: {
Authorization: `Token ${token}`
}
});

// Make query
const {data} = await client.post('/query', {
workspaceId,
sql,
cacheRefresh: refresh,
cacheTTL: ttl
});

// Wait query to finish by checking the status
await pRetry(async () => {
const {data: queryStatus} = await client.get(`/query/${data.id}?workspaceId=${workspaceId}`);

// If status is not FINISHED or FAILED, check again after one second
if (queryStatus.status !== 'FINISHED' && queryStatus.status !== 'FAILED') {
await delay(1000);
throw new Error('not finished');
}

return queryStatus;
});

const {data: limitResult} = await client.get(`/query/${data.id}/result?workspaceId=${workspaceId}&limit=10000&offset=0`);
return limitResult.result;
}

const run = async () => {
console.time();
const data = await getData({
refresh: false,
ttl: 86400,
sql: 'select * from supplier_81391'
});
console.log(data);
console.timeEnd();
}

run().catch(console.error)

PUT /query/:id/cancel

取消執行中的 SQL Query

Query String

namedescription
idQuery 的 ID

Input

namedescription
workspaceId-

Response

  • status: 200