Limitations of SQL API

collinT
collinT Member
in APIs & Domo Developer

I wanted to confirm a few limitations on the SQL API:

  1. Documentation states that joins aren't allowed, seems this extends to subqueries and CTE's as well?
  2. UPDATE and ALTER statements are not supported through the SQL API

Best Answer

  • ArborRose
    ArborRose Coach
    Answer ✓

    Based on documentation I have seen regarding Domo's SQL API, the following limitations apply:

    1. Joins, Subqueries, and CTEs:
      • Joins are not allowed in the SQL API. This limitation extends to subqueries and Common Table Expressions (CTEs) as they are essentially advanced forms of joins and require similar underlying database operations.
    2. UPDATE and ALTER Statements:
      • UPDATE and ALTER statements are not supported through the SQL API. The SQL API is primarily designed for querying data and does not support data modification or schema-altering operations.

    These limitations are in place to ensure performance and security within the Domo environment. If you need to perform more complex operations involving joins or schema changes, you might need to use other tools within Domo or operations outside Domo before ingesting the data.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • ArborRose
    ArborRose Coach
    Answer ✓

    Based on documentation I have seen regarding Domo's SQL API, the following limitations apply:

    1. Joins, Subqueries, and CTEs:
      • Joins are not allowed in the SQL API. This limitation extends to subqueries and Common Table Expressions (CTEs) as they are essentially advanced forms of joins and require similar underlying database operations.
    2. UPDATE and ALTER Statements:
      • UPDATE and ALTER statements are not supported through the SQL API. The SQL API is primarily designed for querying data and does not support data modification or schema-altering operations.

    These limitations are in place to ensure performance and security within the Domo environment. If you need to perform more complex operations involving joins or schema changes, you might need to use other tools within Domo or operations outside Domo before ingesting the data.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • collinT
    collinT Member

    Thanks for the quick response! Did you find documentation that specifically says that? Just curious if I'm missing documentation somewhere, all I've been able to find is what is listed here.

    I'm good with those limitations, joins would be nice but I understand it is not a RDMS so that makes sense, and I don't want end users updating or altering tables so that's actually a plus.

  • ArborRose
    ArborRose Coach

    There used to be a page, but it appears to have been moved or removed.

    https://developer.domo.com/docs/apis/data-apis/sql-api

    If you look at the answers to a recent question I posted, you will see that new SQL functionality is right around the corner.

    https://community-forums.domo.com/main/discussion/67352/sql-to-magic-etl-issue

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories