[
https://issues.apache.org/jira/browse/DRILL-8235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17570413#comment-17570413
]
ASF GitHub Bot commented on DRILL-8235:
---------------------------------------
cgivre commented on code in PR #2585:
URL: https://github.com/apache/drill/pull/2585#discussion_r928194017
##########
contrib/storage-googlesheets/src/test/java/org/apache/drill/exec/store/googlesheets/TestGoogleSheetsWriter.java:
##########
@@ -0,0 +1,135 @@
+/*
+ * 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;
+
+import com.fasterxml.jackson.databind.ObjectMapper;
+import org.apache.drill.categories.RowSetTest;
+import org.apache.drill.common.util.DrillFileUtils;
+import org.apache.drill.exec.oauth.PersistentTokenTable;
+import org.apache.drill.exec.physical.rowSet.RowSet;
+import org.apache.drill.exec.store.StoragePluginRegistry;
+import org.apache.drill.exec.store.StoragePluginRegistry.PluginException;
+import org.apache.drill.shaded.guava.com.google.common.base.Charsets;
+import org.apache.drill.shaded.guava.com.google.common.io.Files;
+import org.apache.drill.test.ClusterFixture;
+import org.apache.drill.test.ClusterTest;
+import org.apache.drill.test.QueryBuilder.QuerySummary;
+import org.junit.BeforeClass;
+import org.junit.Ignore;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+
+import java.nio.file.Paths;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
+import java.util.Map;
+
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+@Category(RowSetTest.class)
+@Ignore("These tests require a live Google Sheets connection. Please run
manually.")
+public class TestGoogleSheetsWriter extends ClusterTest {
+ private static final String AUTH_URI =
"https://accounts.google.com/o/oauth2/auth";;
+ private static final String TOKEN_URI =
"https://oauth2.googleapis.com/token";;
+ private static final List<String> REDIRECT_URI = new
ArrayList<>(Arrays.asList("urn:ietf:wg:oauth:2.0:oob", "http://localhost";));
+
+ private static StoragePluginRegistry pluginRegistry;
+ private static String accessToken;
+ private static String refreshToken;
+
+ @BeforeClass
+ public static void init() throws Exception {
+ ClusterTest.startCluster(ClusterFixture.builder(dirTestWatcher));
+ dirTestWatcher.copyResourceToRoot(Paths.get(""));
+
+ String oauthJson =
Files.asCharSource(DrillFileUtils.getResourceAsFile("/tokens/oauth_tokens.json"),
Charsets.UTF_8).read();
+
+ ObjectMapper mapper = new ObjectMapper();
+ Map<String,String> tokenMap = mapper.readValue(oauthJson, Map.class);
+
+ String clientID = tokenMap.get("client_id");
+ String clientSecret = tokenMap.get("client_secret");
+ accessToken = tokenMap.get("access_token");
+ refreshToken = tokenMap.get("refresh_token");
+
+ pluginRegistry = cluster.drillbit().getContext().getStorage();
+ GoogleSheetsStoragePluginConfig config =
GoogleSheetsStoragePluginConfig.builder()
+ .clientID(clientID)
+ .clientSecret(clientSecret)
+ .redirectUris(REDIRECT_URI)
+ .authUri(AUTH_URI)
+ .tokenUri(TOKEN_URI)
+ .build();
+
+ config.setEnabled(true);
+ pluginRegistry.validatedPut("googlesheets", config);
+ }
+
+ @Test
+ public void testBasicCTAS() throws Exception {
+ try {
+ initializeTokens();
+ } catch (PluginException e) {
+ fail(e.getMessage());
+ }
+
+ String query = "CREATE TABLE googlesheets.`test_sheet`.`test_table` (ID,
NAME) AS " +
+ "SELECT * FROM (VALUES(1,2), (3,4))";
+ // Create the table and insert the values
+ QuerySummary insertResults = queryBuilder().sql(query).run();
+ assertTrue(insertResults.succeeded());
+ }
+
+ @Test
+ public void testCTASFromFile() throws Exception {
+ try {
+ initializeTokens();
+ } catch (PluginException e) {
+ fail(e.getMessage());
+ }
+
+ /*String query = "CREATE TABLE googlesheets.`test_sheet`.`test_table` (ID,
NAME) AS " +
+ "SELECT * FROM (VALUES(1,2), (3,4))";*/
+ String sql = "SELECT * FROM table(cp.`data/Drill_Test_Data.xlsx` (type =>
'excel', sheetName => 'MixedSheet'))";
Review Comment:
I removed this and added a comment about it. With the current
implementation, when you execute a CTAS query, you do so with the desired file
name. IE:
```sql
CREATE TABLE googlesheets.myfile AS
SELECT <stuff>
FROM <data>
```
The problem occurs when you go to test that and you need the file ID.
Google's SDK doesn't give you a way that I found to go from file name to file
ID.
> 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)
