SQL: Cross Cluster Search (CCS) support

[elasticmachine]

Original comment by @costin:

We need a story around it when it lands. It's likely going to have some disruptive effect (since it might require async operations during translation which is sync).

[elasticmachine]

Original comment by @jasontedor:

What is xCR?

[elasticmachine]

Original comment by @costin:

What I thought was the acronym for Cross Cluster Replication (or is it CCR?)...

[elasticmachine]

Original comment by @jasontedor:

Cross cluster replication is CCR. What specifically does this have to do with SQL?

[elasticmachine]

Original comment by @costin:

Daran acronyms - I meant cross cluster Search (CCS or xCS); updated the issue accordingly.

[elasticmachine]

Original comment by @jasontedor:

We need a story around it when it lands.

Okay, I am still confused since cross cluster search has already landed. 😇

[elasticmachine]

Original comment by @costin:

/me updates issue again
This issue is made of an old one hence the POV from the past. I was under the impression that for 6.x there were several improvements to be made however I'm clearly out of loop and unaware of where we stand.
Which brings us to the purpose of this issue - trying to figure if and to what degree CCS can be used inside SQL.

[elasticmachine]

Original comment by @jasontedor:

There are always improvements to be made, but cross cluster search is supported as GA functionality in 6.0.0.

[elasticmachine]

Original comment by @nik9000:

So we've actually done all the pre-work required to make cross cluster search work for SQL. I think we could work on this right now if we really wanted it for the alpha.

[elasticmachine]

Original comment by @costin:

Looking at the JDBC spec it looks like the notion of a remote cluster seems to map nicely on the catalog concept supported by JDBC and ODBC which roughly is equivalent to a DB.
Schemas, if we are to support them, would then map to the current security context (user).
The nice thing about catalogs is that, in JDBC (and I assume in ODBC) as well there's a dedicated method for the catalog separator, which we can map to :.
This allows us to map SQL quite nicely to the existing ES syntax for indices.

[elasticmachine]

Original comment by @bpintea:

Indeed, the ODBC driver can return the application the character string to use a catalog name separator (but some earlier standard will always use the . character, which seems now a de-facto standard).