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
| Description |
|---|---|
| CONFIG* | JSONObject Config parameter specifications are available in the below section. |
FIELDS FOR CONFIG JSON
| Key | Description |
|---|---|
| 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. |
| responseFormat* | String
|
| selectedColumns | JSONArray Controls the columns that need to be exported. Sample: ["column1","column2"] |
| showHiddenCols | Boolean Controls whether the columns that have been hidden in the table have to be exported. Default value - false. |
| showPersonalCols | Boolean Controls whether the columns that have been marked as personal data in the table have to be exported. Default value - false. |
| callbackUrl | String 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
| Key | Description |
|---|---|
| delimiter | Integer
|
| recordDelimiter | Integer
|
| quoted | Integer
|
| includeHeader | Boolean To include the column names in the first row of the CSV exported. Default value - true. |
JSON SPECIFIC ATTRIBUTES
| Key | Description |
|---|---|
| keyValueFormat | Boolean To return JSON data as ColumnName - Value pair. Default value - true. |
PDF SPECIFIC ATTRIBUTES
| Key | Description | |
|---|---|---|
| paperSize | Integer
| |
| paperStyle | String To controls the orientation. Portrait/Landscape | |
| showTitle | Integer
| |
| showDesc | Integer
| |
| exportLanguage | Integer
| |
| zoomFactor | Integer 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 1 | The 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
| |
| 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
| Key | Description |
|---|---|
| includeTitle | Integer
|
| includeDesc | Integer
|
POSSIBLE ERROR CODES
Sample Request:
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"
}
}