CODEWITHSUNDEEP
restgoogle-cloud-platformgoogle-bigquery
Vinit
Vinit

Reputation: 31

Creating a Google BigQuery stored procedure through the REST API

I'm trying to create a Google BigQuery stored procedure using the REST API. I tried all the methods of the REST Resource routines. However, it didn't help.

Has anyone ever created a BigQuery stored procedure through the REST API? If so, how?

Upvotes: 3

Views: 9215

Answers (3)

Gibson Lunaziz
Gibson Lunaziz

Reputation: 1163

A few years later...

This is the code i use for create routine in BigQuery via API (prototype). NB: i don't find the setDescription method in the RoutineInfo.Builder...

public void createRoutine(String datasetName, String routineName, String parameters, String script) {
    RoutineId routineId = RoutineId.of(datasetName, routineName);

    RoutineInfo.Builder routineInfoBuilder = RoutineInfo.newBuilder(routineId)
            .setLanguage("SQL")
            .setRoutineType("PROCEDURE")
            .setBody(script);
    if(StringUtils.hasText(parameters)) {
        List<RoutineArgument> arguments = new ArrayList<>();
        List<String> params = List.of(parameters.split(","));
        for (String param : params) {
            String[] ps = param.trim().split(" ");
            String paramName = ps[0];
            StandardSQLDataType dataType = convertType(ps[1]);
            RoutineArgument argument = RoutineArgument.newBuilder()
                    .setName(paramName)
                    .setDataType(dataType)
                    .build();
            arguments.add(argument);

        }
        if(!CollectionUtils.isEmpty(arguments)) {
            routineInfoBuilder.setArguments(arguments).build();
        }
    }
    RoutineInfo routineInfo = routineInfoBuilder.build();

    bigQuery.create(routineInfo);
    log.info("Routine created {}:{}", datasetName, routineName);
}

/*
Supported type : BOOL, INT64, FLOAT64, NUMERIC, STRING, BYTES, STRUCT, ARRAY, TIMESTAMP, DATE, TIME, DATETIME, GEOGRAPHY
 */
private StandardSQLDataType convertType(String type) {
    StandardSQLTypeName r = StandardSQLTypeName.valueOf(type);
    return StandardSQLDataType.newBuilder(r).build();
}

Upvotes: 0

rsantiago
rsantiago

Reputation: 2099

Now it is possible to create a PROCEDURE:

curl --request POST \
  'https://bigquery.googleapis.com/bigquery/v2/projects/my-project/datasets/mydataset/routines?key=[YOUR_API_KEY]' \
  --header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  --header 'Accept: application/json' \
  --header 'Content-Type: application/json' \
  --data '{"routineReference":{"projectId":"my-project","datasetId":"mydataset","routineId":"test_routine"},"routineType":"PROCEDURE","definitionBody":"BEGIN SET y = y + delta;END;","arguments":[{"name":"y","dataType":{"typeKind":"INT64"},"mode":"INOUT"},{"name":"delta","dataType":{"typeKind":"INT64"}}]}' \
  --compressed

Once created you can call it;

DECLARE accumulator INT64 DEFAULT 0;
CALL help.test_routine2(accumulator, 3);
CALL help.test_routine2(accumulator, 6);
SELECT accumulator;

Row accumulator 
1   9

You can Try the API where I tested the command above.

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

As Graham noted on his comment, just issue a SQL query like:

CREATE PROCEDURE mydataset.AddDelta(INOUT x INT64, delta INT64)
BEGIN
  SET x = x + delta;
END;

Do this as any other query with the REST API.

Upvotes: 1

Related Questions