Zoho Analytics CloudSQL - API Specification
SQL QUERYING OVER HTTP USING ZOHO ANALYTICS API
Zoho Analytics has implemented the Zoho CloudSQL technology as an extension to its HTTP Web API. Using the HTTP API, users can query Zoho Analytics Workspace by providing the SQL queries.
Currently Zoho Analytics supports only SQL SELECT Queries. Other SQL statements like INSERT, UPDATE and DELETE will be supported very soon.
Using SQL Select statements developers can fetch data from a single Table/Query Table or joining one or more tables in Zoho Analytics. The data can be fetched in different response formats, which includes CSV, PDF, HTML, JSON and XML
SQL SELECT QUERY REQUEST FORMAT:
Zoho Analytics uses the Export Using Query action request to execute any SQL SELECT query given.
REQUEST URI:
https://<ZohoAnalytics_Server_URI>/api/<OwnerEmail>/<WorkspaceName>
QUERY STRING PARAMETERS:
| Parameter | Possible Values | Description |
|---|---|---|
| ZOHO_ACTION | EXPORT | This parameter specifies the action to be performed by the API request.Note: Value of ZOHO_ACTION parameter should be in the same case(UPPER CASE) as given in this document. |
| ZOHO_OUTPUT_FORMAT | XML/JSON/CSV/PDF/HTML/IMAGE | This parameter specifies the output format for the response. |
| ZOHO_ERROR_FORMAT | XML/JSON | Specifies the output format for the response in case an error occurs when trying to process the request. |
| ZOHO_API_VERSION | 1.0 | The API version of Zoho Analytics based on which the application(/service) has been written. This parameter allows the Zoho Analytics to handle applications based on the older versions.The current API version is 1.0. |
AUTHORIZATION
To make authenticated API request, append the access token in Authorization request header.
| Value | Description |
|---|---|---|
| Authorization | Zoho-oauthtoken<space><access_token> | The Access token provides a secure and temporary access to Zoho Analytics API's. Each access token will be valid only for an hour, and can be used only for the set of operations that is described in the scope. |
SQL QUERY AS POST PARAMETER:
The exact SQL Select query has to be passed as a post parameter to the API request. The query should be passed as a value to the parameter ZOHO_SQLQUERY. The exact SQL Select query string should be URL encoded.
SAMPLE QUERY:
The sample Select Query will fetch all the employees in the 'finance’ department along with their details in CSV Format.
ENTITIES INVOLVED:
Workspace Name: EmployeeDB Tables Involved: Employee, EmpDetails
SAMPLE QUERY:
The sample Select Query will fetch all the employees in the 'finance’ department along with their details in CSV Format.
ENTITIES INVOLVED:
Workspace Name: EmployeeDB Tables Involved: Employee, EmpDetails
SELECT QUERY IN POST PARAMETER: (NOTE THE QUERY SHOULD BE URL ENCODED)
ZOHO_SQLQUERY=select empdet.Name Name,empdet.DOB Date_Of_Birth,empdet.Address Address,emp.BasicSal BasicPay,round(emp.BasicSal + emp.Allowance,2) Salary from Employee emp inner join EmpDetails empdet on emp.ID = empdet.ID where emp.Dept = 'Finance'
URL ENCODED SELECT QUERY IN POST PARAMETER:
&ZOHO_SQLQUERY=select%20empdet.Name%20Name%2Cempdet.DOB%20Date_Of_Birth%2Cempdet.Address%20Address%2Cemp.BasicSal%20BasicPay%2Cround%28emp.BasicSal%20%2B%20emp.Allowance%2C2%29%20Salary%20from%20Employee%20emp%20inner%20join%20EmpDetails%20empdet%20on%20emp.ID%20%3D%20empdet.ID%20where%20emp.Dept%20%3D%20%27Finance%27
SAMPLE SUCCESS RESPONSE:
The sample response for the above query in CSV format is given below. The first row of the CSV response will contain the column names:
Name,Date_Of_Birth,Address,BasicPay,SalaryAkram,"10 Dec, 1979 00:00:00",california, $10,000 , $10,500....