copy_to_and_scan.slt

# Basic tests for copy to.

statement ok
COPY ( SELECT 1 AS a, 2 AS b ) TO gcs
	OPTIONS (
        service_account_key = '${GCP_SERVICE_ACCOUNT_KEY}',
        bucket = '${GCS_BUCKET_NAME}',
        location = 'copy_to/with_opts.csv'
	);

query II
SELECT * FROM csv_scan(
	'gs://${GCS_BUCKET_NAME}/copy_to/with_opts.csv',
	service_account_key => '${GCP_SERVICE_ACCOUNT_KEY}'
);
----
1	2

statement ok
COPY ( SELECT 3 AS a, 4 AS b )
	TO 'gs://${GCS_BUCKET_NAME}/copy_to/with_url.csv'
	OPTIONS (
        service_account_key = '${GCP_SERVICE_ACCOUNT_KEY}',
	);

query II
SELECT b, a FROM csv_scan(
	'gs://${GCS_BUCKET_NAME}/copy_to/with_url.csv',
	service_account_key => '${GCP_SERVICE_ACCOUNT_KEY}'
);
----
4	3

# Credentials
statement ok
CREATE CREDENTIALS gcp_creds PROVIDER gcp
	( service_account_key '${GCP_SERVICE_ACCOUNT_KEY}' );

statement ok
COPY ( SELECT 5 AS a, 6 AS b )
	TO 'gs://${GCS_BUCKET_NAME}/copy_to/with_creds.csv'
	CREDENTIALS gcp_creds;

query II
SELECT a, b FROM csv_scan(
	'gs://${GCS_BUCKET_NAME}/copy_to/with_creds.csv',
	gcp_creds
);
----
5	6

# Multiple URLs

query II rowsort
SELECT a, b FROM csv_scan(
	[
		'gs://${GCS_BUCKET_NAME}/copy_to/with_opts.csv',
		'gs://${GCS_BUCKET_NAME}/copy_to/with_url.csv',
		'gs://${GCS_BUCKET_NAME}/copy_to/with_creds.csv'
	],
	service_account_key => '${GCP_SERVICE_ACCOUNT_KEY}'
);
----
1	2
3	4
5	6

query II rowsort
SELECT a, b FROM csv_scan(
	[
		'gs://${GCS_BUCKET_NAME}/copy_to/with_opts.csv',
		'gs://${GCS_BUCKET_NAME}/copy_to/with_url.csv',
		'gs://${GCS_BUCKET_NAME}/copy_to/with_creds.csv'
	],
	gcp_creds
);
----
1	2
3	4
5	6

query II rowsort
SELECT a, b FROM csv_scan(
	'gs://${GCS_BUCKET_NAME}/copy_to/with_*.csv',
	gcp_creds
);
----
1	2
3	4
5	6

# Test multiple URLs with globs (and different kinds of globs).

statement ok
COPY ( SELECT 7 AS a, 8 AS b )
	TO 'gs://${GCS_BUCKET_NAME}/copy_to_with_creds.csv'
	CREDENTIALS gcp_creds;

# Found a bug out of the blue, previous code took the prefix for search as
# "abc" when querying glob pattern "abc*". Since object_store adds a "/",
# prefix in this case should be None.

query II rowsort
SELECT a, b FROM csv_scan(
	[
		'gs://${GCS_BUCKET_NAME}/copy_to*',
		'gs://${GCS_BUCKET_NAME}/**/with_*.csv'
	],
	gcp_creds
);
----
1	2
3	4
5	6
7	8