POST /ls/api/data/sqlQuery

##Overview The sqlQuery api is a way to send custom queries to be run directly on the sql database of a community. It returns results in two formats, TableData or Items, depending on the input to the API.

##Post format:

• OAuth2 authorization header, (Authorization bearer token)
  • See OAuth2
• Post Parameters
  1. query
     • String representing the SQL Query to be run
  2. itemType (optional)
     • String representing the item type the results should be returned as
     • When itemType is not specified, results will be returned in TableData format
     • When itemType is specified, results will be returned in Item format
     • If specifying itemType, the query issued must include an h_id column that is selected from the table that corresponds to the specified itemType
  3. faultBlocks (optional)
     • boolean representing whether or not want blocks on the item faulted in automatically
     • Only used if itemType is specified, otherwise it is ignored
  4. tableType (optional)
     • possible valid values are Item, History, and Transaction. Represents whether the query is for the normal Item table, History table, or Transaction table. If you query a history or transaction table and do not set this properly, number of entries returned and faulting of blocks may not work properly
     • Only used if itemType is specified, otherwise it is ignored
  5. queryName (optional)
     • String representing the name of the query. The purpose of setting this is to allow for the gathering and aggregating of runtime metrics for all queries submitted with the same name. Metrics for each queryName can be viewed at a later time using the MetricType SQLQueryAPI.

##Examples

###TableData request

resp = requests.post(server + '/ls/api/data/sqlQuery',
                headers={'Authorization':'Bearer ' + accessToken},
                data={'query':'select path, level, display from liquid_dimension_dimension limit 3'})

###TableData Server Response

[
  {
    "path": "/Data/communication[Work]",
    "level": "/Data/communication",
    "display": "Work"
  },
  {
    "path": "/Data/notificationType[CustomerNotifications]",
    "level": "/Data/notificationType",
    "display": "Customer Notifications"
  },
  {
    "path": "/Data/mediaType[Help]/subType[Other]",
    "level": "/Data/mediaType/subType",
    "display": "Help Other"
  }
]

###Items request

resp = requests.post(server + '/ls/api/data/sqlQuery',
                headers={'Authorization':'Bearer ' + accessToken},
                data={
                    'query':'select * from liquid_dimension_dimension limit 1'
                    'itemType':'Dimension',
                    'faultBlocks':true})

###Items Server Response

[
  {
    "headers": {
      "sourceId": "3e189074-c9c3-e723-320c-edb02004780c",
      "configData": "true",
      "updatedByUserId": "[email protected]",
      "type": "Dimension",
      "createdAt": "1420666195742",
      "applicationTime": "0",
      "client": "Liquid",
      "action": "Update",
      "state": "InterfaceBatch",
      "id": "36471e8c62646c9577732afb78bd679d893a22d4",
      "clientId": "36471e8c62646c9577732afb78bd679d893a22d4",
      "community": "Liquid",
      "revisionId": "94e2bebc-0585-4675-ab1b-0c9b634ab55a",
      "processedAt": "1420666537942",
      "category": "dimension"
    },
    "data": {
      "path": "/Data/communication[Work]",
      "level": "/Data/communication",
      "alias": "",
      "display": "Work",
      "description": "",
      "sortOrder": 1,
      "depth": 1
    }
  }
]

###Notes

• The sqlQuery API will return at most 5000 results, regardless of the actual number of rows the provided query would have returned
  • This API is not meant for mass retrieval of data
• The sqlQuery API is only capable of executing read only queries. Any statements that would alter table data are rejected
• Queries issued to the sqlQuery API have 30 seconds to execute, at which point they will timeout
  • Long running or complicated queries should not be issued through this API