Skip to content

GoogleDrive-batchsource.md

Latest commit

 

History

History
220 lines (154 loc) · 11.7 KB

GoogleDrive-batchsource.md

File metadata and controls

220 lines (154 loc) · 11.7 KB
Raw

Google Drive Batch Source

Description

Reads a fileset from specified Google Drive directory via Google Drive API.

Properties

Basic

Identifier Type: Identifier specifies whether the given ID for Google Drive entity is a file or directory.

Directory Identifier: Identifier of the source folder.

This comes after folders/ in the URL. For example, if the URL is

https://drive.google.com/drive/folders/1dyUEebJaFnWa3Z4n0BFMVAXQ7mfUH11g?resourcekey=0-XVijrJSp3E3gkdJp20MpCQ

Then the Directory Identifier would be 1dyUEebJaFnWa3Z4n0BFMVAXQ7mfUH11g.

File Identifier: Identifier of the file.

This comes after file/d/ or document/d/ or spreadsheets/d/ in the URL. For example, if the URL is

https://docs.google.com/file/d/17W3vOhBwe0i24OdVNsbz8rAMClzUitKeAbumTqWFrkows

Then the File Identifier would be 17W3vOhBwe0i24OdVNsbz8rAMClzUitKeAbumTqWFrkows.
Note: Either Directory Identifier or File Identifier should have a value.

File Metadata Properties: Properties that represent metadata of files. They will be a part of output structured record. Descriptions for properties can be view at Drive API file reference.

Structured Schema Required : Set this property to true if the output should be structured records. Using structured records allows you to use read files with formats like avro, csv, json, text, xls, etc.

Format: Format of the data to read. The format must be one of 'avro', 'blob', 'csv', 'delimited', 'json', 'parquet', 'text', 'tsv', 'xls', or the name of any format plugin that you have deployed to your environment. If the format is a macro, only the pre-packaged formats can be used. If the format is 'blob', every input file will be read into a separate record. The 'blob' format also requires a schema that contains a field named 'body' of type 'bytes'. If the format is 'text', the schema must contain a field named 'body' of type 'string'.

Get Schema: Auto-detects schema from file. Supported formats are: avro, parquet, csv, delimited, tsv, blob, xls and text.

Blob - is set by default as field named 'body' of type bytes.

Text - is set by default as two fields: 'body' of type bytes and 'offset' of type 'long'.

JSON - is not supported, user has to manually provide the output schema.

Parquet - If the path is a directory, the plugin will look for files ending in '.parquet' to read the schema from. If no such file can be found, an error will be returned.

Avro - If the path is a directory, the plugin will look for files ending in '.avro' to read the schema from. If no such file can be found, an error will be returned.

Sample Size: The maximum number of rows that will get investigated for automatic data type detection. The default value is 1000. This is used when the format is xls, csv, tsv, delimited.

Override: A list of columns with the corresponding data types for whom the automatic data type detection gets skipped. This is used when the format is xls, csv, tsv, delimited.

Delimiter: Delimiter to use when the format is 'delimited'. This will be ignored for other formats.

Enable Quoted Values Whether to treat content between quotes as a value. This value will only be used if the format is 'csv', 'tsv' or 'delimited'. For example, if this is set to true, a line that looks like 1, "a, b, c" will output two fields. The first field will have 1 as its value and the second will have a, b, c as its value. The quote characters will be trimmed. The newline delimiter cannot be within quotes.

It also assumes the quotes are well enclosed. The left quote will match the first following quote right before the delimiter. If there is an unenclosed quote, an error will occur.

Use First Row as Header: Whether to use the first line of each file as the column headers. Supported formats are 'text', 'csv', 'tsv', 'xls', 'delimited'.

Terminate Reading After Empty Row: Specify whether to stop reading after encountering the first empty row. Defaults to false. When false the reader will read all rows in the sheet. This is only used when the format is 'xls'.

Select Sheet Using: Select the sheet by name or number. Default is 'Sheet Number'. This is only used when the format is 'xls'.

Sheet Value: The name/number of the sheet to read from. If not specified, the first sheet will be read. Sheet Numbers are 0 based, ie first sheet is 0. This is only used when the format is 'xls'.

Filtering

Filter: Filter that can be applied to the files in the selected directory. Filters follow the Google Drive filters syntax.

Modification Date Range: Filter that narrows set of files by modified date range. User can select either among predefined or custom entered ranges. For Custom selection the dates range can be specified via Start Date and End Date.

Start Date: Start date for custom modification date range. Is shown only when Custom range is selected for Modification Rate Range field. RFC3339 format, default timezone is UTC, e.g., 2012-06-04T12:00:00-08:00.

End Date: End date for custom modification date range. Is shown only when Custom range is selected for Modification Date Range field. RFC3339 format, default timezone is UTC, e.g., 2012-06-04T12:00:00-08:00.

File Types To Pull: Types of files which should be pulled from a specified directory. The following values are supported: binary (all non-Google Drive formats), Google Documents, Google Spreadsheets, Google Drawings, Google Presentations and Google Apps Scripts. For Google Drive formats user should specify exporting format in Exporting section.

Authentication

Authentication Type: Type of authentication used to access Google API.

OAuth2 and Service Account types are available.

Make sure that:

  • Google Drive API is enabled in the GCP Project.
  • Google Drive Folder is shared to the service account email used with the required permission.

OAuth2 Properties

OAuth2 client credentials can be generated on Google Cloud Credentials Page

OAuth Method: The method used to get OAuth access tokens. Users have the option to either directly provide the OAuth access token or supply a client ID, client secret, and refresh token.

Access Token: Short lived access token used for connecting.

Client ID: OAuth2 client id used to identify the application.

Client Secret: OAuth2 client secret used to access the authorization server.

Refresh Token: OAuth2 refresh token to acquire new access tokens.

For more details on OAuth2, see Google Drive Api Documentation

Service Account Type

Make sure that the Google Drive Folder is shared with the specified service account email. Viewer role must be granted to the specified service account to read files from the Google Drive Folder.

  • Service Account File Path: Path on the local file system of the service account key used for authorization.

    Can be set to 'auto-detect' when running on a Dataproc cluster which needs to be created with the following scopes:

    When running on other clusters, the file must be present on every node in the cluster.

  • Service Account JSON: Contents of the service account JSON file. Service Account JSON can be generated on Google Cloud Service Account page

Advanced

Maximum Partition Size: Maximum body size for each structured record specified in bytes. Default 0 value means unlimited. Is not applicable for files in Google formats.

Body Output Format Output format for body of file. "Bytes" and "String" values are available.

File System Properties: Additional properties to use with the InputFormat when reading the data.

File Encoding: The character encoding for the file(s) to be read. The default encoding is UTF-8.

Exporting

Google Documents Export Format: MIME type which is used for Google Documents when converted to structured records.

Google Spreadsheets Export Format: MIME type which is used for Google Spreadsheets when converted to structured records.

Google Drawings Export Format: MIME type which is used for Google Drawings when converted to structured records.

Google Presentations Export Format: MIME type which is used for Google Presentations when converted to structured records.

Steps to Generate OAuth2 Credentials

  1. Create credentials for the Client ID and Client Secret properties here.

  2. On the Create OAuth client ID page, under Authorized redirect URIs, specify a URI of http://localhost:8080. This is just to generate the refresh token.

  3. Click Create. The OAuth client is created. For more information, see this doc.

  4. Copy the Client ID and Client Secret to the plugin properties.

  5. To get the Refresh Token, follow these steps:

    1. Authenticate and authorize with the Google Auth server to get an authorization code.
    2. Use that authorization code with the Google Token server to get a refresh token that the plugin will use to get future access tokens.

    To get the authorization code, you can copy the URL below, change to use your client_id, and then open that URL in a browser window.

    https://accounts.google.com/o/oauth2/v2/auth?
scope=https%3A//www.googleapis.com/auth/drive.readonly&
access_type=offline&
include_granted_scopes=true&
response_type=code&                  
state=state_parameter_passthrough_value&
redirect_uri=http%3A//localhost:8080&
client_id=199375159079-st8toco9pfu1qi5b45fkj59unc5th2v1.apps.googleusercontent.com

    This will prompt you to login, authorize this client for specified scopes, and then redirect you to http://localhost:8080. It will look like an error page, but notice that the URL of the error page redirected to include the code. In a normal web application, that is how the authorization code is returned to the requesting web application.

    For example, URL of the page will be something like

    http://localhost:8080/?state=state_parameter_passthrough_value&code=4/0AX4XfWi6PsiJiPO4MjltrcD6uoRgwci-HX16aL1-Ax-tgqYgC47NnjtCCKRoVzv46m8aJw&scope=https://www.googleapis.com/auth/drive

    Here, code=4/0AX4XfWi6PsiJiPO4MjltrcD6uoRgwci-HX16aL1-Ax-tgqYgC47NnjtCCKRoVzv46m8aJw.

    NOTE: If you see an error like this Authorization Error — Error 400: admin_policy_enforced, then the GCP User’s organization has a policy that restricts you from using Client IDs for third party products. In that case, they’ll need to get that restriction lifted, or use a different GCP user in a different org.

    With that authorization code, you can now call the Google Token server to get the access token and the refresh token in the response. Set the code, client_id, and client_secret in the curl command below and run it in a Cloud Shell terminal.

    curl -X POST -d "code=4/0AX4XfWjgRdrWXuNxqXOOtw_9THZlwomweFrzcoHMBbTFkrKLMvo8twSXdGT9JramIYq86w&client_id=199375159079-st8toco9pfu1qi5b45fkj59unc5th2v1.apps.googleusercontent.com&client_secret=q2zQ-vc3wG5iF5twSwBQkn68&redirect_uri=http%3A//localhost:8080&grant_type=authorization_code&access_type=offline" \
https://oauth2.googleapis.com/token

  6. Now, you will have your refresh_token, which is the last OAuth 2.0 property that the Google Drive Batch Source needs to authorize with the Google Drive API.