[
https://issues.apache.org/jira/browse/DRILL-8235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17562745#comment-17562745
]
ASF GitHub Bot commented on DRILL-8235:
---------------------------------------
github-code-scanning[bot] commented on code in PR #2585:
URL: https://github.com/apache/drill/pull/2585#discussion_r913978697
##########
contrib/storage-googlesheets/src/main/java/org/apache/drill/exec/store/googlesheets/utils/GoogleSheetsUtils.java:
##########
@@ -0,0 +1,534 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.drill.exec.store.googlesheets.utils;
+
+import com.google.api.client.auth.oauth2.AuthorizationCodeFlow;
+import com.google.api.client.auth.oauth2.Credential;
+import com.google.api.client.auth.oauth2.DataStoreCredentialRefreshListener;
+import com.google.api.client.auth.oauth2.StoredCredential;
+import
com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
+import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
+import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
+import com.google.api.client.json.JsonFactory;
+import com.google.api.client.json.gson.GsonFactory;
+import com.google.api.client.util.store.DataStore;
+import com.google.api.services.sheets.v4.Sheets;
+import com.google.api.services.sheets.v4.Sheets.Spreadsheets.Values.BatchGet;
+import com.google.api.services.sheets.v4.SheetsScopes;
+import com.google.api.services.sheets.v4.model.AddSheetRequest;
+import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest;
+import com.google.api.services.sheets.v4.model.Request;
+import com.google.api.services.sheets.v4.model.Sheet;
+import com.google.api.services.sheets.v4.model.SheetProperties;
+import com.google.api.services.sheets.v4.model.Spreadsheet;
+import com.google.api.services.sheets.v4.model.SpreadsheetProperties;
+import com.google.api.services.sheets.v4.model.UpdateValuesResponse;
+import com.google.api.services.sheets.v4.model.ValueRange;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.expression.SchemaPath;
+import org.apache.drill.exec.oauth.OAuthTokenProvider;
+import org.apache.drill.exec.record.metadata.SchemaBuilder;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
+import org.apache.drill.exec.store.googlesheets.DrillDataStore;
+import org.apache.drill.exec.store.googlesheets.GoogleSheetsColumn;
+import
org.apache.drill.exec.store.googlesheets.GoogleSheetsStoragePluginConfig;
+import
org.apache.drill.exec.store.googlesheets.columns.GoogleSheetsColumnRange;
+import org.apache.drill.exec.util.Utilities;
+import org.apache.parquet.Strings;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import java.io.IOException;
+import java.security.GeneralSecurityException;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Map.Entry;
+
+import static com.google.api.client.util.Strings.isNullOrEmpty;
+
+
+public class GoogleSheetsUtils {
+
+ private static final Logger logger =
LoggerFactory.getLogger(GoogleSheetsUtils.class);
+ private static final int SAMPLE_SIZE = 5;
+ private static final JsonFactory JSON_FACTORY =
GsonFactory.getDefaultInstance();
+ private static final String UNKNOWN_HEADER = "field_";
+
+ /**
+ * Represents the possible data types found in a GoogleSheets document
+ */
+ public enum DATA_TYPES {
+ /**
+ * Represents a field before the datatype is known
+ */
+ UNKNOWN,
+ /**
+ * A numeric data type, either a float or an int. These are all
+ * converted to Doubles when projected.
+ */
+ NUMERIC,
+ /**
+ * A string data type
+ */
+ VARCHAR,
+ /**
+ * A field containing a date
+ */
+ DATE,
+ /**
+ * A field containing a time
+ */
+ TIME,
+ /**
+ * A field containing timestamps.
+ */
+ TIMESTAMP
+ }
+
+ /**
+ * Returns a Google Credential Object which shall be used to authenticate
calls to the Google Sheets service
+ * @param config The Drill GoogleSheets config
+ * @return An authorized Credential
+ * @throws IOException in the event of network or other connectivity issues,
throws an IOException
+ * @throws GeneralSecurityException In the event the credentials are
incorrect, throws a Security Exception
+ */
+ public static Credential authorize(GoogleSheetsStoragePluginConfig config,
+ DataStore<StoredCredential> dataStore,
+ OAuthTokenProvider tokenProvider,
+ String pluginName,
+ String queryUser) throws IOException,
GeneralSecurityException {
+ GoogleClientSecrets clientSecrets = config.getSecrets();
+ GoogleAuthorizationCodeFlow flow;
+ List<String> scopes = Collections.singletonList(SheetsScopes.SPREADSHEETS);
+
+ if (dataStore == null) {
+ logger.debug("Datastore is null");
+ } else if (dataStore.getDataStoreFactory() == null) {
+ logger.debug("Datastore factory is null");
+ }
+
+ flow = new GoogleAuthorizationCodeFlow.Builder
+ (GoogleNetHttpTransport.newTrustedTransport(), JSON_FACTORY,
clientSecrets, scopes)
+ .setDataStoreFactory(dataStore.getDataStoreFactory())
+ .setAccessType("offline")
+ .build();
+
+ return loadCredential(queryUser, flow, dataStore);
+ }
+
+ public static Credential loadCredential(String userId,
GoogleAuthorizationCodeFlow flow, DataStore<StoredCredential>
credentialDataStore) throws IOException {
+
+ // No requests need to be performed when userId is not specified.
+ if (isNullOrEmpty(userId)) {
+ return null;
+ }
+
+ if (credentialDataStore == null) {
+ return null;
+ }
+ Credential credential = newCredential(userId, flow, credentialDataStore);
+ StoredCredential stored =
((DrillDataStore<StoredCredential>)credentialDataStore).getStoredCredential(userId);
+ if (stored == null) {
+ return null;
+ }
+ credential.setAccessToken(stored.getAccessToken());
+ credential.setRefreshToken(stored.getRefreshToken());
+
credential.setExpirationTimeMilliseconds(stored.getExpirationTimeMilliseconds());
+
+ return credential;
+ }
+
+ /**
+ * Returns a new credential instance based on the given user ID.
+ *
+ * @param userId user ID or {@code null} if not using a persisted credential
store
+ */
+ private static Credential newCredential(String userId, AuthorizationCodeFlow
flow, DataStore<StoredCredential> credentialDataStore) {
+ Credential.Builder builder =
+ new Credential.Builder(flow.getMethod())
+ .setTransport(flow.getTransport())
+ .setJsonFactory(flow.getJsonFactory())
+ .setTokenServerEncodedUrl(flow.getTokenServerEncodedUrl())
+ .setClientAuthentication(flow.getClientAuthentication())
+ .setRequestInitializer(flow.getRequestInitializer())
+ .setClock(flow.getClock());
+
+ if (credentialDataStore != null) {
+ builder.addRefreshListener(
+ new DataStoreCredentialRefreshListener(userId, credentialDataStore));
+ }
+ builder.getRefreshListeners().addAll(flow.getRefreshListeners());
+ return builder.build();
+ }
+
+
+
+ public static Sheets getSheetsService(GoogleSheetsStoragePluginConfig config,
+ DataStore<StoredCredential> dataStore,
+ OAuthTokenProvider tokenProvider,
+ String pluginName,
+ String queryUser)
+ throws IOException, GeneralSecurityException {
+ Credential credential = GoogleSheetsUtils.authorize(config, dataStore,
tokenProvider, pluginName, queryUser);
+ return new Sheets.Builder(
+ GoogleNetHttpTransport.newTrustedTransport(),
GsonFactory.getDefaultInstance(), credential)
+ .setApplicationName("Drill")
+ .build();
+ }
+
+ /**
+ * Returns the title of the GoogleSheet corresponding with the sheetID.
This is the human readable
+ * name of the actual GoogleSheet document.
+ * @param service An authenticated GoogleSheet service
+ * @param sheetID The sheetID. This can be obtained from the sheet URL
+ * @return The title of the sheet document.
+ * @throws IOException
+ */
+ public static String getSheetTitle (Sheets service, String sheetID) throws
IOException {
+ Spreadsheet spreadsheet = service.spreadsheets().get(sheetID).execute();
+ SpreadsheetProperties properties = spreadsheet.getProperties();
+ return properties.getTitle();
+ }
+
+ /**
+ * Returns a list of the titles of the available spreadsheets within a given
Google sheet.
+ * @param service The Google Sheets service
+ * @param sheetID The sheetID for the Google sheet. This can be obtained
from the URL of your Google sheet
+ * @return A list of spreadsheet names within a given Google Sheet
+ * @throws IOException If the Google sheet is unreachable or invalid.
+ */
+ public static List<Sheet> getSheetList(Sheets service, String sheetID)
throws IOException {
+ Spreadsheet spreadsheet = service.spreadsheets().get(sheetID).execute();
+ return spreadsheet.getSheets();
+ }
+
+ /**
+ * Converts a column index to A1 notation. Google sheets has a limitation of
approx 18k
+ * columns, but that is not enforced here. The column index must be greater
than zero or
+ * the function will return null.
+ *
+ * References code found here:
+ * <a
href="https://stackoverflow.com/questions/21229180/convert-column-index-into-corresponding-column-letter";>Stack
Overflow Article</a>
+ * @param column The column index for the desired column. Must be greater
than zero
+ * @return The A1 representation of the column index.
+ */
+ public static String columnToLetter(int column) {
+ if (column <= 0) {
+ return null;
+ }
+
+ int temp = 0;
+ StringBuilder letter = new StringBuilder();
+ while (column > 0) {
+ temp = (column - 1) % 26;
+ letter.insert(0, (char) (temp + 65));
+ column = (column - temp - 1) / 26;
+ }
+ return letter.toString();
+ }
+
+ /**
+ * Given a column reference in A1 notation, this function will
+ * return the column numeric index. GoogleSheets has a limit of approx
+ * 18k columns, but that is not enforced here.
+ *
+ * References code found here:
+ * <a
href="https://stackoverflow.com/questions/21229180/convert-column-index-into-corresponding-column-letter";>Stack
Overflow Article</a>
+ * @param letter The desired column in A1 notation
+ * @return The index of the supplied column
+ */
+ public static int letterToColumnIndex(String letter) {
+ // Make sure the letters are all upper case.
+ letter = letter.toUpperCase();
+ int column = 0;
+ int length = letter.length();
+ for (int i = 0; i < length; i++) {
+ column += (Character.codePointAt(letter, i) - 64) * Math.pow(26, length
- i - 1);
Review Comment:
## Implicit narrowing conversion in compound assignment
Implicit cast of source type double to narrower destination type int.
[Show more
details](https://github.com/apache/drill/security/code-scanning/33)
> Add Storage Plugin for Google Sheets
> ------------------------------------
>
> Key: DRILL-8235
> URL: https://issues.apache.org/jira/browse/DRILL-8235
> Project: Apache Drill
> Issue Type: Improvement
> Components: Storage - Other
> Affects Versions: 1.20.1
> Reporter: Charles Givre
> Assignee: Charles Givre
> Priority: Major
> Fix For: 2.0.0
>
>
> Google Sheets is a very commonly used data source among business users.
> Presto and other query engines do include integrations with Google Sheets and
> so it would be useful for Drill to add this functionality.
> The proposed plugin supports both reading and writing to Google Sheets.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
