Bloque anónimo en el servicio de consultas

El servicio de consultas de Adobe Experience Platform admite bloques anónimos. La función de bloque anónimo permite encadenar una o más sentencias SQL que se ejecutan en secuencia. También permiten la opción de la gestión de excepciones.

La función de bloques anónimos es una forma eficaz de realizar una secuencia de operaciones o consultas. La cadena de consultas dentro del bloque se puede guardar como plantilla y programarse para ejecutarse a una hora o intervalo determinados. Estas consultas se pueden utilizar para escribir y anexar datos para crear un nuevo conjunto de datos y, normalmente, se utilizan donde tiene una dependencia.

La tabla proporciona un desglose de las secciones principales del bloque: ejecución y control de excepciones. Las secciones están definidas por las palabras clave BEGIN, END y EXCEPTION.

Cabe señalar que un bloque es una instrucción ejecutable y, por lo tanto, se puede anidar dentro de otros bloques.

Sample anonymous block queries

The following query shows an example of chaining SQL statements. See the SQL syntax in Query Service document for more information on any of the SQL syntax used.

$$ BEGIN
    CREATE TABLE ADLS_TABLE_A AS SELECT * FROM ADLS_TABLE_1....;
    ....
    CREATE TABLE ADLS_TABLE_D AS SELECT * FROM ADLS_TABLE_C....;
    EXCEPTION WHEN OTHERS THEN SET @ret = SELECT 'ERROR';
END
$$;

In the example below, SET persists the result of a SELECT query in the specified local variable. The variable is scoped to the anonymous block.

The snapshot ID is stored as a local variable (@current_sid). It is then used in the next query to return results based on the SNAPSHOT from the same dataset/table. For more information on the snapshot clause see the SQL syntax documentation.

$$ BEGIN
  SET @current_sid = SELECT parent_id  FROM (SELECT history_meta('your_table_name')) WHERE  is_current = true;
  CREATE temp table abcd_temp_table AS SELECT count(1) FROM your_table_name  SNAPSHOT SINCE @current_sid;
END
$$;

Anonymous block with third-party clients third-party-clients

Certain third-party clients may require a separate identifier before and after an SQL block to indicate that a part of the script should be handled as a single statement. If you receive an error message when using Query Service with a third-party client, you should refer to the documentation of the third-party client regarding the use of an SQL block.

For example, DbVisualizer requires that the delimiter must be the only text on the line. In DbVisualizer, the default value for the Begin Identifier is --/ and for the End Identifier it is /. An example of an anonymous block in DbVisualizer is seen below:

--/
$$ BEGIN
    CREATE TABLE ADLS_TABLE_A AS SELECT * FROM ADLS_TABLE_1....;
    ....
    CREATE TABLE ADLS_TABLE_D AS SELECT * FROM ADLS_TABLE_C....;
    EXCEPTION WHEN OTHERS THEN SET @ret = SELECT 'ERROR';
END
$$;
/

For DbVisualizer in particular, there is also an option in the UI to "Execute the complete buffer as one SQL statement". See the DbVisualizer documentation for more information.

Próximos pasos

By reading this document, you now have a clear understanding of anonymous blocks and how they are structured. Please read the query execution guide for more information on writing queries.

You should also read about how anonymous blocks are used with the incremental load design pattern to increase query efficiency.