How to create and use a variable

  • 15 May 2023
  • 2 replies

Hi, I am new to Streamsets, started using it today.

I have a task to create some entries in a big query table, that represent the number of records in returned by an API call.

I can do this using power automate, without issue, but power automate doesn’t support posting the BigQuery.

roughly speaking the process I have in power automate is….

  1. Http get 
  2. array is returned
  3. get length of the array
  4. post the result to DB table 

I need to use Streamsets and BQ as these have been specified as a requirement.

thanks in advance

2 replies

Userlevel 2

So to understand the ask, You are performing a Get Request, Counting the number of results within a field and posting only the count of those results to a Big Query table?


We can do that, it would look something like this in the canvas

  1. Get the data from your API of choice (ive used UK Company house data in my example)
  2. Using Expression Evaluator we will get the Length of the Field Result 
    ${length(record:value("/previous_company_names"))} which you can name the field how you like but i called mine /interestingcount (Prefixing fields with a forward slash is almost a must in SDC)


  3. Probably Dump all the fields you dont want, but keep a record key and the result column
  4. Store it in BigQuery

Let me know if this works for you or not


Kind regards




Thanks for responding.

I don’t want to count the number of items within what is basically a sublist on a record returned, as your example shows, but literally the number of records returned from the API.