Se connecter à Google BigQuery avec SAS/ACCESS to JDBC

Cet article présente un exemple de syntaxe pour se connecter à Google BigQuery à parti de SAS/ACCESS interface to JDBC.

JDBC ?

Les normes JDBC (Java Database Connectivity) fournissent une interface commune à diverses sources de données, notamment PostgreSQL, MySQL et d’autres sources de données compatibles avec JDBC. C'est le cas , vous l'avez compris, Google BigQuery. L'objectif de JDBC est de permettre l'accès aux données à partir de n'importe quelle application, quel que soit le SGBD qui gère les données. Plus précisément, les normes JDBC spécifient des interfaces de programmation d'application (API) permettant aux applications telles que SAS d'accéder à une base de données. J'ai déjà aborder SAS/ACCESS Interface to JDBC dans mon article Se connecter à une base de données via JDBCPré-requis

Pour vous connecter à une base de données Google Big Query via JDBC, il est nécessaire de disposer: Du module SAS/ACCESS interface to JDBC, D'un driver JDBC permettant l'accès à Big Query, Magnitude Simba drivers for BigQuery, D'un accès à Big Query, un compte de service par exemple.

Instruction libname

Voici un exemple d'instruction libname pour se connecter à Big Query via JDBC :

1
2
3
4
libname bq_jdbc jdbc driverclass="com.simba.googlebigquery.jdbc42.Driver"

URL="jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=franih;OAuthType=0;OAuthServiceAcctEmail=xxxx@xxxx.iam.gserviceaccount.com;OAuthPvtKeyPath=/opt/sas/install/bigquery.json"

classpath="/opt/sas/install/AccessClients/9.4/JDBC/BigQuery"

SCHEMA = "mybigquery";

Le paramètre de libname driverclass indique la classe java (FQCN) à utiliser, dans notre cas com.simba.googlebigquery.jdbc42.Driver
Le paramètre libname url contient la chaîne de connexion

jdbc:bigquery://[Host]:[Port];ProjectId=[Project];OAuthType=[AuthValue];[Property1]=[Value1];[Property2]=[Value2];...

Dans mon exemple, j'utilise un compte de service Google. Je dois alors fournir un compte GoogleService (xxxx@xxxx.iam.gserviceaccount.com) et le chemin d'accès complet à la clé privée du compte de service. Il est possible d'utiliser soit la clé au format p12 soit un fichier json, comme avec un accès avec SAS/ACCESS Interface to Big Query.

Pour utiliser un compte de service l'option OAuthType doit être positionné à 0. Dans l'url il faut également positionner l'id de notre projet via la propriété ProjectId.

La propriété OAuthServiceAcctEmail permet de spécifier l'adresse email du compte de service.

La propriété OAuthPvtKeyPath permet de spécifier l'accès au fichier contenant la clé associée au compte de service.

Le paramètre de libname classpath indique l'emplacement des jars jdbc. Le paramètre de libname schema indique le nom du dataset contenant les tables. Il est ensuite possible d’interroger ses données comme avec n'importe quelle module SAS/ACCESS. Exemple :

1
proc print data=bq_jdbc.mybigdata;run;

En explicit-passthrough

1
2
3
4
5
6
7
8
9
10
11
12
13
14
proc sql;

connect using bq_jdbc ;

create table new_sas_ds as

select * from connection to bq_jdbc (select * from mybigquery.mybigdata);

disconnect from bq_jdbc;quit;

proc sql;

connect to jdbc (driverclass="com.simba.googlebigquery.jdbc42.Driver"

URL="jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=franih;OAuthType=0;OAuthServiceAcctEmail=xxxx@xxxx.iam.gserviceaccount.com;OAuthPvtKeyPath=/opt/sas/install/bigquery.json"

classpath="/opt/sas/install/AccessClients/9.4/JDBC/BigQuery"

SCHEMA = "mybigquery") ;

create table new_sas_ds as

select * from connection to jdbc (select * from mybigquery.mybigdata);

disconnect from jdbc;

quit;

Quelques messages d'erreurs

ERROR: Error trying to establish connection: [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error:toDerInputStream rejects tag type 45. Ce message d'erreur se produit lorsque le pilote JDBC ne parvient pas à lire le fichier p12 ou le fichier json. Vérifiez le contenu du fichier. Error trying to establish connection: [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error : 403 Forbidden Ce problème se produit lorsque l'utilisateur ou le compte de service ne dispose pas des droits pour se connecter à Big Query. Error trying to establish connection: java.sql.SQLNonTransientConnectionException: [Simba][JDBC](10100) Connexion refusée: [Simba][JDBC](11640) Clé(s) de connexion requise(s): OAuthPvtKeyPath; [Simba][JDBC](11480) Clé(s) de connexion optionnelle(s): AdditionalProjects, AllowLargeResults, DefaultDataset, EnableHighThroughPutAPI, FetchThreadsPerResultSet, ForceHTAPI, HighThroughPutMinTableSize, KMSKeyName, Language, LargeResultDataset, LargeResultTable, MaxResults, OAuthAccessToken, OAuthClientId, OAuthClientSecret, OAuthRefreshToken, ProxyHost, RequestGoogleDriveScope, StringColumnLength, Timeout, TimestampFallback, useQueryCache Ce problème se produit lorsque des propriété jdbc sont manquantes dans l'url de connexion.

Activer les logs du pilote JDBC

Pour activer le mode debug du pilote JDBC, ajoutez les propriétés ci-dessous : LogLevel=6 logpath=/tmp Ce qui donne : URL="jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=franih;OAuthType=0;OAuthServiceAcctEmail=xxxx@xxxxx.iam.gserviceaccount.com;OAuthPvtKeyPath=/opt/sas/install/bigquery.json;LogLevel=6;logpath=/tmp"

Le mot de la fin

Si vous souhaitez aller plus loin, je vous invite également à lire mon article Premiers pas avec SAS/ACCESS interface to Big Query pour vous familiariser avec Google BigQuery