Insert into select

Hi,

While running the query (see below) and passing the credentials for both the buckets, I am seeing this in the logs.

|00000000| 48 54 54 50 2f 31 2e 31 20 32 30 30 20 4f 4b 0d |HTTP/1.1 200 OK.|
|00000010| 0a 43 6f 6e 74 65 6e 74 2d 4c 65 6e 67 74 68 3a |.Content-Length:|
|00000020| 20 34 30 36 0d 0a 43 6f 6e 74 65 6e 74 2d 54 79 | 406..Content-Ty|
|00000030| 70 65 3a 20 61 70 70 6c 69 63 61 74 69 6f 6e 2f |pe: application/|
|00000040| 6a 73 6f 6e 3b 20 76 65 72 73 69 6f 6e 3d 32 2e |json; version=2.|
|00000050| 30 2e 30 2d 4e 31 51 4c 0d 0a 44 61 74 65 3a 20 |0.0-N1QL..Date: |
|00000060| 54 75 65 2c 20 32 39 20 4d 61 79 20 32 30 31 38 |Tue, 29 May 2018|
|00000070| 20 30 39 3a 30 38 3a 34 35 20 47 4d 54 0d 0a 0d | 09:08:45 GMT...|
|00000080| 0a 7b 0a 22 72 65 71 75 65 73 74 49 44 22 3a 20 |.{."requestID": |
|00000090| 22 31 37 38 32 30 35 61 36 2d 34 66 35 33 2d 34 |"178205a6-4f53-4|
|000000a0| 30 64 39 2d 62 32 64 38 2d 64 65 38 30 34 66 66 |0d9-b2d8-de804ff|
|000000b0| 62 66 38 32 63 22 2c 0a 22 73 69 67 6e 61 74 75 |bf82c",."signatu|
|000000c0| 72 65 22 3a 20 7b 22 69 64 22 3a 22 6a 73 6f 6e |re": {"id":"json|
|000000d0| 22 7d 2c 0a 22 72 65 73 75 6c 74 73 22 3a 20 5b |"},."results": [|
|000000e0| 0a 5d 2c 0a 22 65 72 72 6f 72 73 22 3a 20 5b 7b |.],."errors": [{|
|000000f0| 22 63 6f 64 65 22 3a 31 33 30 31 34 2c 22 6d 73 |"code":13014,"ms|
|00000100| 67 22 3a 22 55 73 65 72 20 64 6f 65 73 20 6e 6f |g":"User does no|
|00000110| 74 20 68 61 76 65 20 63 72 65 64 65 6e 74 69 61 |t have credentia|
|00000120| 6c 73 20 74 6f 20 72 75 6e 20 49 4e 53 45 52 54 |ls to run INSERT|
|00000130| 20 71 75 65 72 69 65 73 20 6f 6e 20 74 68 65 20 | queries on the |
|00000140| 67 70 63 32 20 62 75 63 6b 65 74 2e 20 41 64 64 |gpc2 bucket. Add|
|00000150| 20 72 6f 6c 65 20 71 75 65 72 79 5f 69 6e 73 65 | role query_inse|
|00000160| 72 74 20 6f 6e 20 67 70 63 32 20 74 6f 20 61 6c |rt on gpc2 to al|
|00000170| 6c 6f 77 20 74 68 65 20 71 75 65 72 79 20 74 6f |low the query to|
|00000180| 20 72 75 6e 2e 22 7d 5d 2c 0a 22 73 74 61 74 75 | run."}],."statu|
|00000190| 73 22 3a 20 22 73 74 6f 70 70 65 64 22 2c 0a 22 |s": "stopped",."|
|000001a0| 6d 65 74 72 69 63 73 22 3a 20 7b 22 65 6c 61 70 |metrics": {"elap|
|000001b0| 73 65 64 54 69 6d 65 22 3a 20 22 38 30 2e 30 30 |sedTime": "80.00|
|000001c0| 32 33 6d 73 22 2c 22 65 78 65 63 75 74 69 6f 6e |23ms","execution|
|000001d0| 54 69 6d 65 22 3a 20 22 38 30 2e 30 30 32 33 6d |Time": "80.0023m|
|000001e0| 73 22 2c 22 72 65 73 75 6c 74 43 6f 75 6e 74 22 |s","resultCount"|
|000001f0| 3a 20 30 2c 22 72 65 73 75 6c 74 53 69 7a 65 22 |: 0,"resultSize"|
|00000200| 3a 20 30 2c 22 65 72 72 6f 72 43 6f 75 6e 74 22 |: 0,"errorCount"|
|00000210| 3a 20 31 7d 0a 7d 0a                            |: 1}.}.         |

It says that I need to provide rights to insert but I am trying to copy data from one bucket to another by passing credentials of both bucket. Each bucket user has access to its own bucket. This is the code I am using:

	@GetMapping(value="/copyDataFromPmcToGpc2")
	public void copyDataFromPmcToGpc2() {
	 String statement = "UPSERT INTO gpc2 (key _k, value _v) SELECT meta().id _k, _v FROM pmc _v returning meta().id";
	 N1qlParams n1qlParams = N1qlParams.build().withCredentials(bucketName, bucketPassword).withCredentials(backup_bucketName, backup_bucketPassword);
	 N1qlQuery n1qlQuery = N1qlQuery.simple(statement, n1qlParams);
	 System.out.println("n1qlQuery: " + n1qlQuery);
	 Observable<AsyncN1qlQueryResult> product = mainCouchbaseTemplate.queryN1QL(n1qlQuery).doOnNext(res -> res.info().forEach(t -> System.out.println("N1qlMetrics: " + t)));
	 List<JsonObject> listOfJsons = product.flatMap(AsyncN1qlQueryResult::rows)
		.map(result -> {return result.value();})
		.toList()
		.toBlocking()
		.single();
	 System.out.println(listOfJsons);
	 
	}

Can someone shed light on how to copy data from one bucket to another using the UPSERT and SELECT in java.

PS: mainCouchbaseTemplate is the couchbase template for pmc bucket.

Thanks,
Himanshu

What to do here depends on what version of Couchbase you are running, because permissions handling changed when we introduced Role-Based Access Control in 5.0.

Are you running a Couchbase version that doesn’t have RBAC, i.e. bucket passwords only?
If so, you should need to provide bucket/bucket-password for gpc2 and pmc.

If you are running a version that has RBAC, you should supply user-id/user-password for a user who has these three roles:
Query Insert (gpc2)
Query Update (gpc2)
Query Select (pmc)

Actually, because of the RETURNING clause, you may need Query Select (gpc2) also.

We are using RBAC. Thanks for the information.