Using SQL Query

Create an export job using SQL select statement to initiate data export.

REQUEST URI

https://<ZohoAnalytics_Server_URI>/restapi/v2/bulk/workspaces/<workspace-id>/data

Get

oauthscope: ZohoAnalytics.data.read

QUERY PARAMETERS

 

Parameter Name		Description
CONFIG*JSONObject
Config parameter specifications are available in the below section.

FIELDS FOR CONFIG JSON

KeyDescription
sqlQuery*String

SQL SELECT Query.
Using SQL Select statements data can be exported from a single Table/Query Table or joining one or more tables.
Check here for more details.
responseFormat*

String

The format of the data to be exported.

  • csv
  • json
  • xml
  • xls
  • pdf
  • html
selectedColumnsJSONArray

Controls the columns that need to be exported.

Sample: ["column1","column2"]
showHiddenColsBoolean

Controls whether the columns that have been hidden in the table have to be exported.
Default value - false.
showPersonalColsBoolean

Controls whether the columns that have been marked as personal data in the table have to be exported.
Default value - false.
callbackUrlString

A valid URL, that should allow HTTP Post method.
The Bulk Export Job's details is posted to this URL on successful completion of job or on failure of job.

CSV SPECIFIC ATTRIBUTES

KeyDescription
delimiter

Integer

The delimiter character used for separating the fields in a row in the CSV.

  • 0 - COMMA
  • 1 - TAB
  • 2 - SEMICOLON
  • 3 - SPACE
recordDelimiter

Integer

The record delimiter (newline character) to use.

  • 0 - DOS
  • 1 - UNIX
  • 2 - MAC
quoted

Integer

The quote character to use for quoting the values.

  • 0 - SINGLE
  • 1 - DOUBLE
includeHeaderBoolean

To include the column names in the first row of the CSV exported.
Default value - true.

JSON SPECIFIC ATTRIBUTES

KeyDescription
keyValueFormatBoolean

To return JSON data as ColumnName - Value pair.
Default value - true.

PDF SPECIFIC ATTRIBUTES

KeyDescription
paperSize

Integer

The size of the paper.

  • 0 - LETTER
  • 1 - LEGAL
  • 2 - TABLOID
  • 3 - A3
  • 4 - A4
  • 5 - AUTO
paperStyleString

To controls the orientation.
Portrait/Landscape
showTitle

Integer

To controls the title positioning.

  • 0 - AT TOP
  • 1 - AT BOTTOM
  • 2 - NONE
showDesc

Integer

To controls the description positioning.

  • 0 - AT TOP
  • 1 - AT BOTTOM
  • 2 - NONE
exportLanguage

Integer

PDF will be rendered using the specified language.

  • 0 - ENGLISH
  • 1 - CHINESE
  • 2 - JAPANESE
  • 3 - EUROPEAN
  • 4 - KOREAN


Default value - 0 (ENGLISH).

zoomFactorInteger

To control the zoom factor for the PDF.
Between 0 to 100
Default value - 100.
Margin Settings:
topMargin
bottomMargin
leftMargin
rightMargin		Decimal values between 0 to 1The margin in inches for that edge. Can be decimal between 0 to 1 (like 0.5).
 
Header/Footer Settings:
leftHeader
rightHeader
centerHeader
leftFooter
rightFooter
centerFooter

Integer

The header or footer value that needs to be generated for each page at that particular position.

  • 0 - Leave it blank
  • 1 - Include Title
  • 2 - Current Date/Time
  • 3 - Include Page number in the format “Page #”
  • 4 - Include page number in the format “Page # Of #”
  • 5 - CUSTOM - Include custom text in footer
Custom Header/Footer value
leftHeaderText
rightHeaderText
centerHeaderText
leftFooterText
rightFooterText
centerFooterText		String

If any of the header/footer setting is 5 (.ie, CUSTOM) then the corresponding custom value/text should be passed.

HTML SPECIFIC ATTRIBUTES

KeyDescription
includeTitle

Integer

To contol the title positioning.
Default value - 0.

  • 0 - AT TOP
  • 1 - AT BOTTOM
  • 2 - NONE
includeDesc

Integer

To contol the description positioning.
Default value - 0.

  • 0 - AT TOP
  • 1 - AT BOTTOM
  • 2 - NONE

POSSIBLE ERROR CODES

7103 , 7403 , 8002 , 8046 , 8119 , 8504

Sample Request:

CurlCsharpGoJavaPHPPythonNode JSDeluge
Copiedcurl https://analyticsapi.zoho.com/restapi/v2/bulk/workspaces/<workspace-id>/data?CONFIG=<encoded_json_value> 
-X 'GET' 
-H 'ZANALYTICS-ORGID: <org-id>' 
-H 'Authorization: Zoho-oauthtoken <access_token>'
Copiedusing System;
using System.Collections.Generic;
using ZohoAnalytics;

namespace ZohoAnalyticsTest
{
    class Program
    {
        long orgId = 55522777;
        long workspaceId = 35130000001055707;

        public void InitiateBulkExportUsingSQL(IAnalyticsClient ac)
        {
            string responseFormat = "csv";
            String sqlQuery = "select * from Sales";
            IBulkAPI data = ac.GetBulkInstance(orgId, workspaceId);
            long jobId = data.InitiateBulkExportUsingSQL(sqlQuery, responseFormat, null);
            Console.WriteLine(jobId);
        }

        static void Main(string[] args)
        {
            string clientId = "1000.xxxxxxx";
            string clientSecret = "xxxxxxx";
            string refreshToken = "1000.xxxxxxx.xxxxxxx";

            try
            {
                IAnalyticsClient ac = new AnalyticsClient(clientId, clientSecret, refreshToken);
                Program obj = new Program();
                obj.InitiateBulkExportUsingSQL(ac);
            }
            catch (ServerException ex)
            {
                Console.WriteLine("Server exception - " + ex.GetErrorMessage());
            }
            catch (Exception ex)
            {
                Console.WriteLine("Other exception - " + ex.Message);
            }
        }
    }
}
Copiedpackage main

import (
    "fmt"
    ZAnalytics "zoho/pkg/analyticsclient"
)

var(
    clientId = "1000.xxxxxxx"
    clientSecret = "xxxxxxx"
    refreshToken = "1000.xxxxxxx.xxxxxxx"

    orgId = "55522777"
    workspaceId = "35130000001055707"
)

func InitiateBulkExportUsingSQL(ac ZAnalytics.Client) {
    config := map[string]interface{}{}
    sqlquery := "select * from Sales"
    responseformat := "csv"
    bulk := ZAnalytics.GetBulkInstance(&ac, orgId, workspaceId)
    result, err := bulk.InitiateBulkExportUsingSQL(sqlquery, responseformat, config)

    if(err != nil){
        fmt.Println(err.ErrorMessage)
    }else{
        fmt.Println(result)
    }
}

func main() {

    ac := ZAnalytics.GetAnalyticsClient(clientId, clientSecret, refreshToken)
    InitiateBulkExportUsingSQL(ac)

}

Copiedimport com.zoho.analytics.client.*;
import org.json.*;

public class Test {

    private long orgId = 55522777l;
    private long workspaceId = 35130000001055707l;

    public static void main(String args[]){

        String clientId = "1000.xxxxxxx";
        String clientSecret = "xxxxxxx";
        String refreshToken = "1000.xxxxxxx.xxxxxxx";

        Test tObj = new Test();
        AnalyticsClient ac = new AnalyticsClient(clientId, clientSecret, refreshToken);

        try {
            tObj.initiateBulkExportUsingSQL(ac);
        }
        catch (ServerException ex) {
            System.out.println("Server exception - ErrorCode : " + ex.getErrorCode() + ", ErrorMessage : "  + ex.getErrorMessage());
        }
        catch (ParseException ex) {
            System.out.println("Parser exception - ErrorMessage : "  + ex.getResponseMessage());
        }
        catch (Exception ex) {
            System.out.println("Other exception - ");
            ex.printStackTrace();
        }
    }

    public void initiateBulkExportUsingSQL(AnalyticsClient ac) throws Exception {
        String sqlQuery = "select * from Sales";
        String responseFormat = "csv";
        BulkAPI bulk = ac.getBulkInstance(orgId, workspaceId);
        long jobId = bulk.initiateBulkExportUsingSQL(sqlQuery, responseFormat, null);
        System.out.println(jobId);
    }
}

Copied<?php

    require 'AnalyticsClient.php';

    class Test
    {
        public $ac = NULL;
        public $client_id = "1000.xxxxxxx";
        public $client_secret = "xxxxxxx";
        public $refresh_token = "1000.xxxxxxx.xxxxxxx";

        public $org_id = "55522777";
        public $workspace_id = "35130000001055707";

        function __construct() {
            $this->ac =  new AnalyticsClient($this->client_id, $this->client_secret, $this->refresh_token);
        }

        function initiateBulkExportUsingSQL() {
            $sql_query = "select * from Sales";
            $response_format = "csv";
            $bulk = $this->ac->getBulkInstance($this->org_id, $this->workspace_id);
            $response = $bulk->initiateBulkExportUsingSQL($sql_query, $response_format);
            print_r($response);
        }
    }

    $test_obj = new Test();

    try {
        $test_obj->initiateBulkExportUsingSQL();
    }
    catch(ServerException $se) {
        echo "Server exception : " . $se->getErrorMessage() . "\n";
    }
    catch(IOException $ioe) {
        echo "IO exception : " . $ioe->getErrorMessage() . "\n";
    }
    catch(ParseException $pe) {
        echo "Parser exception : " . $pe->getErrorMessage() . "\n";
    }
    catch(Exception $e) {
        echo "Exception : " . $e->getErrorMessage() . "\n";
    }
?>

Copiedfrom __future__ import with_statement
from AnalyticsClient import AnalyticsClient
import sys
import json

class Config:

    CLIENTID = "1000.xxxxxxx";
    CLIENTSECRET = "xxxxxxx";
    REFRESHTOKEN = "1000.xxxxxxx.xxxxxxx";

    ORGID = "55522777";
    WORKSPACEID = "35130000001055707";

class sample:

    ac = AnalyticsClient(Config.CLIENTID, Config.CLIENTSECRET, Config.REFRESHTOKEN)

    def initiate_bulk_export_using_sql(self, ac):
        response_format = "CSV"
        sql_query = "select * from Sales where Region='East'"
        bulk = ac.get_bulk_instance(Config.ORGID, Config.WORKSPACEID)
        result = bulk.initiate_bulk_export_using_sql(sql_query, response_format)     
        print(result)

try:
    obj = sample()
    obj.initiate_bulk_export_using_sql(obj.ac);

except Exception as e:
    print(str(e))

Copiedvar analyticsClient = require('./AnalyticsClient');

var clientId = '1000.xxxxxxx';
var clientSecret = 'xxxxxxx';
var refreshtoken = '1000.xxxxxxx.xxxxxxx';
var orgId = '55522777';
var workspaceId = '35130000001055707';

var ac = new analyticsClient(clientId, clientSecret, refreshtoken);

var config = {};
var responseFormat = 'csv';
var sqlQuery = 'select * from Sales';
var bulk = ac.getBulkInstance(orgId, workspaceId);
bulk.initiateBulkExportUsingSQL(sqlQuery, responseFormat).then((response) => {
    console.log(response);

}).catch((error) => {
    console.log('errorCode : '+error.errorCode);
    console.log('errorMessage : '+error.errorMessage);
});

CopiedorgId = "55522777";
workspaceId = "35130000001055707";

headersMap = Map();
headersMap.put("ZANALYTICS-ORGID",orgId);

config = Map();
config.put("responseFormat","csv");
config.put("sqlQuery","select * from Sales");
paramsMap = Map();
paramsMap.put("CONFIG",config.toString());
response = invokeurl
[
  url :"https://analyticsapi.zoho.com/restapi/v2/bulk/workspaces/" + workspaceId + "/data"
  type :GET
  parameters:paramsMap
  headers:headersMap
  connection:"analytics_oauth_connection"
];
info response;

Download client libraries:  C# | GO | JAVA | PHP | PYTHON | NodeJS

Sample value for CONFIG parameter:

Copied{
"responseFormat":"csv",
"sqlQuery":"select * from Sales"
}

Sample Request:

CopiedHTTP/1.1 200 OK
Content-Type:application/json;charset=UTF-8

{
    "status": "success",
    "summary": "Create bulk export job",
    "data": {
        "jobId": "35130000001391001"
    }
}