Snowflake connector 200-row cap in UiPath Integration Service: how to paginate or go async

Описание и применение

При использовании нового коннектора Snowflake в UiPath синхронный запрос возвращает максимум 200 строк, даже если в SQL указано больше. Решение – разбивать запрос на страницы с LIMIT/OFFSET или использовать асинхронный запрос, который также ограничен 200 строками, но можно последовательно получать страницы.

Задача

When using UiPath’s new Snowflake connector, Execute Select Query Synchronously returns at most 200 rows even if the SQL LIMIT is higher, causing large result sets to be truncated.

Результат

By paging with LIMIT/OFFSET or using asynchronous execution, workflows can retrieve datasets of any size from Snowflake while respecting the 200-row cap of the connector.

Рабочий процесс

• Write a stable SELECT query with ORDER BY on a unique key and LIMIT 200 OFFSET 0.
• Generate the output schema from the literal query; then replace the offset with a variable.
• In a Do While loop, execute Execute Select Query Synchronously, append the returned rows to a collection, and increment the offset by 200.
• Stop when a page returns fewer than 200 rows.
• (If the query takes >30s) use Execute Any Query Asynchronously to get a Query ID, poll status with Get Asynchronous Query Execution Status, and when SUCCESS, retrieve results with Get Asynchronous Query Execution Results, paging with its Offset and Limit properties.

Доказательства

Execute Select Query Synchronously returns max 200 records even with a higher LIMIT; use LIMIT/OFFSET in the Select Query inside a Do While

Источник

Always paginate large result sets with LIMIT and OFFSET

Источник

Use Execute Any Query Asynchronously for queries over 30 seconds; non-SELECT commands only via the async activity

Источник

Without ORDER BY, LIMIT results are non-deterministic; keep ORDER BY and LIMIT at the same query level

Источник