[GitHub] [drill] github-code-scanning[bot] commented on a diff in pull request #2585: DRILL-8235: Add Storage Plugin for Google Sheets

[GitBox <gi...@apache.org>]

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)



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org