Create Query Table

Create a query table in the specified workspace.

REQUEST URI

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

Post

oauthscope: ZohoAnalytics.modeling.create

QUERY PARAMETERS

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

FIELDS FOR CONFIG JSON

Key	Description
sqlQuery^*	String SQL query to construct the query table.
queryTableName*	String Name of the query table to be created.
description	String The description of the query table.
folderId	String Id of the folder where the query table have to be created.

POSSIBLE ERROR CODES

7301 , 7407 , 7362 , 7402 , 7403 , 7409 , 7111 , 8507

Sample Request:

Curl Csharp Go Java PHP Python Node JS Deluge

Copiedcurl https://analyticsapi.zoho.com/restapi/v2/workspaces/<workspace-id>/querytables --data-urlencode 'CONFIG={"sqlQuery":"<sql-query>","queryTableName":"<query-table-name>"}' 
-X 'POST' 
-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 CreateQueryTable(IAnalyticsClient ac)
        {
            string sqlQuery = "select * from Sales";
            string queryTableName = "QT_Sales";
            IWorkspaceAPI ws = ac.GetWorkspaceInstance(orgId, workspaceId);
            long viewId = ws.CreateQueryTable(sqlQuery, queryTableName, null);
            Console.WriteLine(viewId);
        }

        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.CreateQueryTable(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 CreateQueryTable(ac ZAnalytics.Client) {
    config := map[string]interface{}{}
    sqlquery:= "Select * from Sales"
    querytablename:= "New_QT"

    workspace := ZAnalytics.GetWorkspaceInstance(&ac, orgId, workspaceId)
    result, exception := workspace.CreateQueryTable(sqlquery, querytablename, config)

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

func main() {

    ac := ZAnalytics.GetAnalyticsClient(clientId, clientSecret, refreshToken)
    CreateQueryTable(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.createQueryTable(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 createQueryTable(AnalyticsClient ac) throws Exception {
        WorkspaceAPI workspace = ac.getWorkspaceInstance(orgId, workspaceId);
        String sqlQuery = "Select * from \"Sales\"";
        String queryTableName = "Java_QT";
        long result = workspace.createQueryTable(sqlQuery, queryTableName, null);
        System.out.println(result);
    }
}

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 createQueryTable() {
            $sql_query = "Select * from Sales";
            $query_table_name = "QT_Sales";

            $workspace = $this->ac->getWorkspaceInstance($this->org_id, $this->workspace_id);
            $response = $workspace->createQueryTable($sql_query, $query_table_name);
            print_r($response);
        }
    }

    $test_obj = new Test();

    try {
        $test_obj->createQueryTable();
    }
    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 create_query_table(self, ac):
        
        sql_query = "select * from Sales"
        query_table_name = "Sales_QT"
        workspace = ac.get_workspace_instance(Config.ORGID, Config.WORKSPACEID)
        result = workspace.create_query_table(sql_query, query_table_name)
        print(result)

try:
    obj = sample()
    obj.create_query_table(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 sqlQuery = '';
var queryTableName = '';

var config = {};
var workspace = ac.getWorkspaceInstance(orgId, workspaceId);
workspace.createQueryTable(sqlQuery, queryTableName, config).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("sqlQuery", "select * from Sales");
config.put("queryTableName", "Sales_QT");
paramsMap = Map();
paramsMap.put("CONFIG",config.toString());
response = invokeurl
[
  url :"https://analyticsapi.zoho.com/restapi/v2/workspaces/" + workspaceId + "/querytables"
  type :POST
  parameters:paramsMap
  headers:headersMap
  connection:"analytics_oauth_connection"
];
info response;

Sample value for CONFIG parameter:

Copied{
    "sqlQuery": "<sql-query>",
    "queryTableName": "<query-table-name>"
}

Sample Response:

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

{
    "status": "success",
    "summary": "Create QueryTable",
    "data": {
        "viewId": "176702400000912004"
    }
}