You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@spark.apache.org by Ryan Blue <rb...@netflix.com.INVALID> on 2019/01/13 21:38:25 UTC
[DISCUSS] Identifiers with multi-catalog support
In the DSv2 sync up, we tried to discuss the Table metadata proposal but
were side-tracked on its use of TableIdentifier. There were good points
about how Spark should identify tables, views, functions, etc, and I want
to start a discussion here.
Identifiers are orthogonal to the TableCatalog proposal that can be updated
to use whatever identifier class we choose. That proposal is concerned with
what information should be passed to define a table, and how to pass that
information.
The main question for *this* discussion is: *how should Spark identify
tables, views, and functions when it supports multiple catalogs?*
There are two main approaches:
1. Use a 3-part identifier, catalog.database.table
2. Use an identifier with an arbitrary number of parts
*Option 1: use 3-part identifiers*
The argument for option #1 is that it is simple. If an external data store
has additional logical hierarchy layers, then that hierarchy would be
mapped to multiple catalogs in Spark. Spark can support show tables and
show databases without much trouble. This is the approach used by Presto,
so there is some precedent for it.
The drawback is that mapping a more complex hierarchy into Spark requires
more configuration. If an external DB has a 3-level hierarchy — say, for
example, schema.database.table — then option #1 requires users to configure
a catalog for each top-level structure, each schema. When a new schema is
added, it is not automatically accessible.
Catalog implementations could use session options could provide a rough
work-around by changing the plugin’s “current” schema. I think this is an
anti-pattern, so another strike against this option is that it encourages
bad practices.
*Option 2: use n-part identifiers*
That drawback for option #1 is the main argument for option #2: Spark
should allow users to easily interact with the native structure of an
external store. For option #2, a full identifier would be an
arbitrary-length list of identifiers. For the example above, using
catalog.schema.database.table is allowed. An identifier would be something
like this:
case class CatalogIdentifier(parts: Seq[String])
The problem with option #2 is how to implement a listing and discovery API,
for operations like SHOW TABLES. If the catalog API requires a list(ident:
CatalogIdentifier), what does it return? There is no guarantee that the
listed objects are tables and not nested namespaces. How would Spark handle
arbitrary nesting that differs across catalogs?
Hopefully, I’ve captured the design question well enough for a productive
discussion. Thanks!
rb
--
Ryan Blue
Software Engineer
Netflix
Re: [DISCUSS] Identifiers with multi-catalog support
Posted by Ryan Blue <rb...@netflix.com.INVALID>.
Xiao,
After reading the document you attached, I don’t think this problem affects
table resolution. The two resolution algorithms are orthogonal.
The 4th step in the algorithm for column resolution is this:
If there is more than one possible match, then the reference is ambiguous
and we are unable to resolve it. In this case, an AnalysisException is
thrown.
If a column reference is ambiguous, then the query fails analysis. There
can be just one match. That rule simplifies the problem quite a bit because
there isn’t some priority for choosing which one to use when a reference is
ambiguous.
The change that the table identifier work will introduce is a larger set of
names that identify tables. Instead of t1 or db1.t1, we will also have
c1.db1.t1. We may also have a situation where db1.t1 exists in two
catalogs, c1 and c2. If that’s the case, then most of the time there is no
problem because tables must be referenced to be added to the space used for
column resolution. In other words, you’d have to join c1.db1.t1 with
c2.db1.t1 to cause a possible conflict. If that query referenced a column
db1.t1.i1 and both tables had column i1, then the reference is ambiguous
and would fail analysis.
There are a few things to note about this:
1. This problem is only “new” in the case of joining across catalogs,
which isn’t currently possible: it won’t catch any existing queries.
2. The same problem happens with ambiguous table references already:
db1.t1 and db2.t1 can already introduce an ambiguous reference
3. If there is a situation where multiple tables match or multiple
columns match due to a conflict between levels, like db and catalog, then
step #4 applies again and analysis fails because of an ambiguous reference.
Likewise for columns.
rb
On Wed, Jan 23, 2019 at 9:58 PM Xiao Li <li...@databricks.com> wrote:
> This is the PR I mentioned in the meeting:
> https://github.com/apache/spark/pull/17185 This was merged into Spark
> 2.4. Now, the traditional DBMS users can easily migrate their previous
> queries to Spark SQL.
>
> *Attached doc* is the design doc by* Sunitha Kambhampati*, who is working
> for IBM BigInsight, STC and then CODAIT. She did a great work! She compared
> Spark with most mainstream DBMS and made Spark consistent with the others.
>
> In the doc, Table B shows the challenges we might face. Basically, the
> database name, catalog name, table name, and column name could be the same.
> Different resolution rules could cause different plans and then Spark will
> return results. Since this is very fundamental, we need to decide it in the
> early stage and also clearly document in the public Spark SQL doc. Our end
> users can follow it.
>
> To ensure we do not introduce the behavior changes, based on my
> understanding, we ONLY can add the new cases after the existing cases
> [which are mentioned in the doc], instead of introducing a brand-new
> resolution rule.
>
> Also, if we consider the nested columns, the cases become more complex.*
> DB Tsai* raised it offline with me multiple times about various issues in
> nested column handling. His company heavily used the nested columns. I
> think we should also consider nested fields in the resolution design;
> otherwise, we could easily break the existing behaviors. Spark SQL does not
> have enough test cases for nested columns, to be honest.
>
> Cheers,
>
> Xiao
>
> On Tue, Jan 22, 2019 at 12:24 PM Ryan Blue <rb...@netflix.com.invalid>
> wrote:
>
>> Thanks for reviewing this! I'll create an SPIP doc and issue for it and
>> call a vote.
>>
>> On Tue, Jan 22, 2019 at 11:41 AM Matt Cheah <mc...@palantir.com> wrote:
>>
>>> +1 for n-part namespace as proposed. Agree that a short SPIP would be
>>> appropriate for this. Perhaps also a JIRA ticket?
>>>
>>>
>>>
>>> -Matt Cheah
>>>
>>>
>>>
>>> *From: *Felix Cheung <fe...@hotmail.com>
>>> *Date: *Sunday, January 20, 2019 at 4:48 PM
>>> *To: *"rblue@netflix.com" <rb...@netflix.com>, Spark Dev List <
>>> dev@spark.apache.org>
>>> *Subject: *Re: [DISCUSS] Identifiers with multi-catalog support
>>>
>>>
>>>
>>> +1 I like Ryan last mail. Thank you for putting it clearly (should be a
>>> spec/SPIP!)
>>>
>>>
>>>
>>> I agree and understand the need for 3 part id. However I don’t think we
>>> should make assumption that it must be or can only be as long as 3 parts.
>>> Once the catalog is identified (ie. The first part), the catalog should be
>>> responsible for resolving the namespace or schema etc. Agree also path is
>>> good idea to add to support file-based variant. Should separator be
>>> optional (perhaps in *space) to keep this extensible (it might not always
>>> be ‘.’)
>>>
>>>
>>>
>>> Also this whole scheme will need to play nice with column identifier as
>>> well.
>>>
>>>
>>>
>>>
>>> ------------------------------
>>>
>>> *From:* Ryan Blue <rb...@netflix.com.invalid>
>>> *Sent:* Thursday, January 17, 2019 11:38 AM
>>> *To:* Spark Dev List
>>> *Subject:* Re: [DISCUSS] Identifiers with multi-catalog support
>>>
>>>
>>>
>>> Any discussion on how Spark should manage identifiers when multiple
>>> catalogs are supported?
>>>
>>>
>>>
>>> I know this is an area where a lot of people are interested in making
>>> progress, and it is a blocker for both multi-catalog support and CTAS in
>>> DSv2.
>>>
>>>
>>>
>>> On Sun, Jan 13, 2019 at 2:22 PM Ryan Blue <rb...@netflix.com> wrote:
>>>
>>> I think that the solution to this problem is to mix the two approaches
>>> by supporting 3 identifier parts: catalog, namespace, and name, where
>>> namespace can be an n-part identifier:
>>>
>>> type Namespace = Seq[String]
>>>
>>> case class CatalogIdentifier(space: Namespace, name: String)
>>>
>>> This allows catalogs to work with the hierarchy of the external store,
>>> but the catalog API only requires a few discovery methods to list
>>> namespaces and to list each type of object in a namespace.
>>>
>>> def listNamespaces(): Seq[Namespace]
>>>
>>> def listNamespaces(space: Namespace, prefix: String): Seq[Namespace]
>>>
>>> def listTables(space: Namespace): Seq[CatalogIdentifier]
>>>
>>> def listViews(space: Namespace): Seq[CatalogIdentifier]
>>>
>>> def listFunctions(space: Namespace): Seq[CatalogIdentifier]
>>>
>>> The methods to list tables, views, or functions, would only return
>>> identifiers for the type queried, not namespaces or the other objects.
>>>
>>> The SQL parser would be updated so that identifiers are parsed to UnresovledIdentifier(parts:
>>> Seq[String]), and resolution would work like this pseudo-code:
>>>
>>> def resolveIdentifier(ident: UnresolvedIdentifier): (CatalogPlugin, CatalogIdentifier) = {
>>>
>>> val maybeCatalog = sparkSession.catalog(ident.parts.head)
>>>
>>> ident.parts match {
>>>
>>> case Seq(catalogName, *space, name) if catalog.isDefined =>
>>>
>>> (maybeCatalog.get, CatalogIdentifier(space, name))
>>>
>>> case Seq(*space, name) =>
>>>
>>> (sparkSession.defaultCatalog, CatalogIdentifier(space, name))
>>>
>>> }
>>>
>>> }
>>>
>>> I think this is a good approach because it allows Spark users to
>>> reference or discovery any name in the hierarchy of an external store, it
>>> uses a few well-defined methods for discovery, and makes name hierarchy a
>>> user concern.
>>>
>>> · SHOW (DATABASES|SCHEMAS|NAMESPACES) would return the result
>>> of listNamespaces()
>>>
>>> · SHOW NAMESPACES LIKE a.b% would return the result of listNamespaces(Seq("a"),
>>> "b")
>>>
>>> · USE a.b would set the current namespace to Seq("a", "b")
>>>
>>> · SHOW TABLES would return the result of
>>> listTables(currentNamespace)
>>>
>>> Also, I think that we could generalize this a little more to support
>>> path-based tables by adding a path to CatalogIdentifier, either as a
>>> namespace or as a separate optional string. Then, the identifier passed to
>>> a catalog would work for either a path-based table or a catalog table,
>>> without needing a path-based catalog API.
>>>
>>> Thoughts?
>>>
>>>
>>>
>>> On Sun, Jan 13, 2019 at 1:38 PM Ryan Blue <rb...@netflix.com> wrote:
>>>
>>> In the DSv2 sync up, we tried to discuss the Table metadata proposal but
>>> were side-tracked on its use of TableIdentifier. There were good points
>>> about how Spark should identify tables, views, functions, etc, and I want
>>> to start a discussion here.
>>>
>>> Identifiers are orthogonal to the TableCatalog proposal that can be
>>> updated to use whatever identifier class we choose. That proposal is
>>> concerned with what information should be passed to define a table, and how
>>> to pass that information.
>>>
>>> The main question for *this* discussion is: *how should Spark identify
>>> tables, views, and functions when it supports multiple catalogs?*
>>>
>>> There are two main approaches:
>>>
>>> 1. Use a 3-part identifier, catalog.database.table
>>>
>>> 2. Use an identifier with an arbitrary number of parts
>>>
>>> *Option 1: use 3-part identifiers*
>>>
>>> The argument for option #1 is that it is simple. If an external data
>>> store has additional logical hierarchy layers, then that hierarchy would be
>>> mapped to multiple catalogs in Spark. Spark can support show tables and
>>> show databases without much trouble. This is the approach used by Presto,
>>> so there is some precedent for it.
>>>
>>> The drawback is that mapping a more complex hierarchy into Spark
>>> requires more configuration. If an external DB has a 3-level hierarchy —
>>> say, for example, schema.database.table — then option #1 requires users
>>> to configure a catalog for each top-level structure, each schema. When a
>>> new schema is added, it is not automatically accessible.
>>>
>>> Catalog implementations could use session options could provide a rough
>>> work-around by changing the plugin’s “current” schema. I think this is an
>>> anti-pattern, so another strike against this option is that it encourages
>>> bad practices.
>>>
>>> *Option 2: use n-part identifiers*
>>>
>>> That drawback for option #1 is the main argument for option #2: Spark
>>> should allow users to easily interact with the native structure of an
>>> external store. For option #2, a full identifier would be an
>>> arbitrary-length list of identifiers. For the example above, using
>>> catalog.schema.database.table is allowed. An identifier would be
>>> something like this:
>>>
>>> case class CatalogIdentifier(parts: Seq[String])
>>>
>>> The problem with option #2 is how to implement a listing and discovery
>>> API, for operations like SHOW TABLES. If the catalog API requires a list(ident:
>>> CatalogIdentifier), what does it return? There is no guarantee that the
>>> listed objects are tables and not nested namespaces. How would Spark handle
>>> arbitrary nesting that differs across catalogs?
>>>
>>> Hopefully, I’ve captured the design question well enough for a
>>> productive discussion. Thanks!
>>>
>>> rb
>>>
>>> --
>>>
>>> Ryan Blue
>>>
>>> Software Engineer
>>>
>>> Netflix
>>>
>>>
>>>
>>>
>>> --
>>>
>>> Ryan Blue
>>>
>>> Software Engineer
>>>
>>> Netflix
>>>
>>>
>>>
>>>
>>> --
>>>
>>> Ryan Blue
>>>
>>> Software Engineer
>>>
>>> Netflix
>>>
>>
>>
>> --
>> Ryan Blue
>> Software Engineer
>> Netflix
>>
>
>
> --
> [image:
> https://databricks.com/sparkaisummit/north-america?utm_source=email&utm_medium=signature]
>
> ---------------------------------------------------------------------
> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
--
Ryan Blue
Software Engineer
Netflix
Re: [DISCUSS] Identifiers with multi-catalog support
Posted by Wenchen Fan <cl...@gmail.com>.
Hi Ryan,
Thanks for your explanation! It makes sense to me, let's go ahead with a
SPIP.
Thanks,
Wenchen
On Mon, Jan 28, 2019 at 2:33 AM Ryan Blue <rb...@netflix.com> wrote:
> I haven’t had a chance to look into Xiao’s concerns yet, but I do want to
> address what Wenchen brought up:
>
> a user’s query may break if someone registers a catalog whose name is same
> with a namespace in the default catalog
>
> The question is what to do if “a” is both a catalog name and a namespace
> in the default catalog. You’re right that adding a catalog could break
> queries.
>
> The alternative, to assume that “a” is a namespace if there is a conflict,
> makes this problem far more likely to occur. The reason is that users can
> also create namespaces. If a query depends on catalog “a” and a user
> creates the namespace “a” in the default catalog, then this also breaks the
> existing query.
>
> I think the right answer is to prefer “a” as a catalog because it is far
> less likely that users will affect other jobs. The proposed way to
> configure catalogs is to add configuration properties to Spark. That is
> done globally by administrators and locally in user sessions. On the other
> hand, when anyone creates a namespace/database, that is always a globally.
> So it is far more likely that users will break each other adding a
> namespace because users don’t add catalogs globally.
>
> Does that make sense?
>
> With the namespace abstraction, is DATABASE an alias of namespace?
>
> I don’t see a downside to using DATABASE as an alias for NAMESPACE.
> Currently, it implies a single identifier. We could enforce that in Spark,
> but I see no reason for extra code to do it. Catalogs that support just one
> level of namespacing will reject namespaces in namespaces or multi-level
> namespaces anyway. No need to do it in Spark.
>
> According to the doc Xiao mentioned before, it seems column name
> resolution is also a hard problem and I’m wondering if the rule needs to be
> changed to fit n-part name.
>
> I agree it’s a problem we need to solve and document, probably with an
> SPIP. I’m not sure that it requires a solution as part of this work, but I
> intend to go through Xiao’s email carefully to determine whether we need to
> do it as part of this.
>
> On Wed, Jan 23, 2019 at 11:17 PM Wenchen Fan <cl...@gmail.com> wrote:
>
>> Hi Ryan,
>>
>> Sorry to be late to this party. I think the n-part namespace is a good
>> idea, thanks for bringing up this great proposal!
>>
>> Here I want to confirm several things to make sure we are on the same
>> page.
>>
>> *1. table name resolution*
>> According to the pseudo-code you provided, for table name `a.b.c`, we
>> will first check if `a` is a registered catalog. If it is, then `b` is the
>> namespace, `c` is the table name. If `a` is not a registered catalog, then
>> `a.b` is the namespace to search in the default catalog, and `c` is the
>> table name.
>>
>> This means, a user's query may break if someone registers a catalog whose
>> name is same with a namespace in the default catalog. I'm not saying this
>> is bad and unacceptable, I just want to bring it to your attention and see
>> if we care about it.
>>
>> This ambiguity is new, as previously the table name can at most have 2
>> parts. So when the table name only has one part, it must be a table under
>> the default database. When the table name has 2 parts, it must be a table
>> under the specified database.
>>
>>
>> *2. namespace*
>> With the namespace abstraction, is DATABASE an alias of namespace? And
>> users can create a DATABASE under a DATABASE?
>>
>>
>> *3. column name resolution*
>> I think column name resolution is very different from table name
>> resolution. When we resolve a column, we have a scope: we know which tables
>> we can resolve the column from. According to the doc Xiao mentioned before,
>> it seems column name resolution is also a hard problem and I'm wondering if
>> the rule needs to be changed to fit n-part name.
>>
>> Thanks,
>> Wenchen
>>
>>
>> On Thu, Jan 24, 2019 at 1:59 PM Xiao Li <li...@databricks.com> wrote:
>>
>>> This is the PR I mentioned in the meeting:
>>> https://github.com/apache/spark/pull/17185 This was merged into Spark
>>> 2.4. Now, the traditional DBMS users can easily migrate their previous
>>> queries to Spark SQL.
>>>
>>> *Attached doc* is the design doc by* Sunitha Kambhampati*, who is
>>> working for IBM BigInsight, STC and then CODAIT. She did a great work! She
>>> compared Spark with most mainstream DBMS and made Spark consistent with the
>>> others.
>>>
>>> In the doc, Table B shows the challenges we might face. Basically, the
>>> database name, catalog name, table name, and column name could be the same.
>>> Different resolution rules could cause different plans and then Spark will
>>> return results. Since this is very fundamental, we need to decide it in the
>>> early stage and also clearly document in the public Spark SQL doc. Our end
>>> users can follow it.
>>>
>>> To ensure we do not introduce the behavior changes, based on my
>>> understanding, we ONLY can add the new cases after the existing cases
>>> [which are mentioned in the doc], instead of introducing a brand-new
>>> resolution rule.
>>>
>>> Also, if we consider the nested columns, the cases become more complex.*
>>> DB Tsai* raised it offline with me multiple times about various issues
>>> in nested column handling. His company heavily used the nested columns. I
>>> think we should also consider nested fields in the resolution design;
>>> otherwise, we could easily break the existing behaviors. Spark SQL does not
>>> have enough test cases for nested columns, to be honest.
>>>
>>> Cheers,
>>>
>>> Xiao
>>>
>>> On Tue, Jan 22, 2019 at 12:24 PM Ryan Blue <rb...@netflix.com.invalid>
>>> wrote:
>>>
>>>> Thanks for reviewing this! I'll create an SPIP doc and issue for it and
>>>> call a vote.
>>>>
>>>> On Tue, Jan 22, 2019 at 11:41 AM Matt Cheah <mc...@palantir.com>
>>>> wrote:
>>>>
>>>>> +1 for n-part namespace as proposed. Agree that a short SPIP would be
>>>>> appropriate for this. Perhaps also a JIRA ticket?
>>>>>
>>>>>
>>>>>
>>>>> -Matt Cheah
>>>>>
>>>>>
>>>>>
>>>>> *From: *Felix Cheung <fe...@hotmail.com>
>>>>> *Date: *Sunday, January 20, 2019 at 4:48 PM
>>>>> *To: *"rblue@netflix.com" <rb...@netflix.com>, Spark Dev List <
>>>>> dev@spark.apache.org>
>>>>> *Subject: *Re: [DISCUSS] Identifiers with multi-catalog support
>>>>>
>>>>>
>>>>>
>>>>> +1 I like Ryan last mail. Thank you for putting it clearly (should be
>>>>> a spec/SPIP!)
>>>>>
>>>>>
>>>>>
>>>>> I agree and understand the need for 3 part id. However I don’t think
>>>>> we should make assumption that it must be or can only be as long as 3
>>>>> parts. Once the catalog is identified (ie. The first part), the catalog
>>>>> should be responsible for resolving the namespace or schema etc. Agree also
>>>>> path is good idea to add to support file-based variant. Should separator be
>>>>> optional (perhaps in *space) to keep this extensible (it might not always
>>>>> be ‘.’)
>>>>>
>>>>>
>>>>>
>>>>> Also this whole scheme will need to play nice with column identifier
>>>>> as well.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ------------------------------
>>>>>
>>>>> *From:* Ryan Blue <rb...@netflix.com.invalid>
>>>>> *Sent:* Thursday, January 17, 2019 11:38 AM
>>>>> *To:* Spark Dev List
>>>>> *Subject:* Re: [DISCUSS] Identifiers with multi-catalog support
>>>>>
>>>>>
>>>>>
>>>>> Any discussion on how Spark should manage identifiers when multiple
>>>>> catalogs are supported?
>>>>>
>>>>>
>>>>>
>>>>> I know this is an area where a lot of people are interested in making
>>>>> progress, and it is a blocker for both multi-catalog support and CTAS in
>>>>> DSv2.
>>>>>
>>>>>
>>>>>
>>>>> On Sun, Jan 13, 2019 at 2:22 PM Ryan Blue <rb...@netflix.com> wrote:
>>>>>
>>>>> I think that the solution to this problem is to mix the two approaches
>>>>> by supporting 3 identifier parts: catalog, namespace, and name, where
>>>>> namespace can be an n-part identifier:
>>>>>
>>>>> type Namespace = Seq[String]
>>>>>
>>>>> case class CatalogIdentifier(space: Namespace, name: String)
>>>>>
>>>>> This allows catalogs to work with the hierarchy of the external store,
>>>>> but the catalog API only requires a few discovery methods to list
>>>>> namespaces and to list each type of object in a namespace.
>>>>>
>>>>> def listNamespaces(): Seq[Namespace]
>>>>>
>>>>> def listNamespaces(space: Namespace, prefix: String): Seq[Namespace]
>>>>>
>>>>> def listTables(space: Namespace): Seq[CatalogIdentifier]
>>>>>
>>>>> def listViews(space: Namespace): Seq[CatalogIdentifier]
>>>>>
>>>>> def listFunctions(space: Namespace): Seq[CatalogIdentifier]
>>>>>
>>>>> The methods to list tables, views, or functions, would only return
>>>>> identifiers for the type queried, not namespaces or the other objects.
>>>>>
>>>>> The SQL parser would be updated so that identifiers are parsed to UnresovledIdentifier(parts:
>>>>> Seq[String]), and resolution would work like this pseudo-code:
>>>>>
>>>>> def resolveIdentifier(ident: UnresolvedIdentifier): (CatalogPlugin, CatalogIdentifier) = {
>>>>>
>>>>> val maybeCatalog = sparkSession.catalog(ident.parts.head)
>>>>>
>>>>> ident.parts match {
>>>>>
>>>>> case Seq(catalogName, *space, name) if catalog.isDefined =>
>>>>>
>>>>> (maybeCatalog.get, CatalogIdentifier(space, name))
>>>>>
>>>>> case Seq(*space, name) =>
>>>>>
>>>>> (sparkSession.defaultCatalog, CatalogIdentifier(space, name))
>>>>>
>>>>> }
>>>>>
>>>>> }
>>>>>
>>>>> I think this is a good approach because it allows Spark users to
>>>>> reference or discovery any name in the hierarchy of an external store, it
>>>>> uses a few well-defined methods for discovery, and makes name hierarchy a
>>>>> user concern.
>>>>>
>>>>> · SHOW (DATABASES|SCHEMAS|NAMESPACES) would return the result
>>>>> of listNamespaces()
>>>>>
>>>>> · SHOW NAMESPACES LIKE a.b% would return the result of listNamespaces(Seq("a"),
>>>>> "b")
>>>>>
>>>>> · USE a.b would set the current namespace to Seq("a", "b")
>>>>>
>>>>> · SHOW TABLES would return the result of
>>>>> listTables(currentNamespace)
>>>>>
>>>>> Also, I think that we could generalize this a little more to support
>>>>> path-based tables by adding a path to CatalogIdentifier, either as a
>>>>> namespace or as a separate optional string. Then, the identifier passed to
>>>>> a catalog would work for either a path-based table or a catalog table,
>>>>> without needing a path-based catalog API.
>>>>>
>>>>> Thoughts?
>>>>>
>>>>>
>>>>>
>>>>> On Sun, Jan 13, 2019 at 1:38 PM Ryan Blue <rb...@netflix.com> wrote:
>>>>>
>>>>> In the DSv2 sync up, we tried to discuss the Table metadata proposal
>>>>> but were side-tracked on its use of TableIdentifier. There were good points
>>>>> about how Spark should identify tables, views, functions, etc, and I want
>>>>> to start a discussion here.
>>>>>
>>>>> Identifiers are orthogonal to the TableCatalog proposal that can be
>>>>> updated to use whatever identifier class we choose. That proposal is
>>>>> concerned with what information should be passed to define a table, and how
>>>>> to pass that information.
>>>>>
>>>>> The main question for *this* discussion is: *how should Spark
>>>>> identify tables, views, and functions when it supports multiple catalogs?*
>>>>>
>>>>> There are two main approaches:
>>>>>
>>>>> 1. Use a 3-part identifier, catalog.database.table
>>>>>
>>>>> 2. Use an identifier with an arbitrary number of parts
>>>>>
>>>>> *Option 1: use 3-part identifiers*
>>>>>
>>>>> The argument for option #1 is that it is simple. If an external data
>>>>> store has additional logical hierarchy layers, then that hierarchy would be
>>>>> mapped to multiple catalogs in Spark. Spark can support show tables and
>>>>> show databases without much trouble. This is the approach used by Presto,
>>>>> so there is some precedent for it.
>>>>>
>>>>> The drawback is that mapping a more complex hierarchy into Spark
>>>>> requires more configuration. If an external DB has a 3-level hierarchy —
>>>>> say, for example, schema.database.table — then option #1 requires
>>>>> users to configure a catalog for each top-level structure, each schema.
>>>>> When a new schema is added, it is not automatically accessible.
>>>>>
>>>>> Catalog implementations could use session options could provide a
>>>>> rough work-around by changing the plugin’s “current” schema. I think this
>>>>> is an anti-pattern, so another strike against this option is that it
>>>>> encourages bad practices.
>>>>>
>>>>> *Option 2: use n-part identifiers*
>>>>>
>>>>> That drawback for option #1 is the main argument for option #2: Spark
>>>>> should allow users to easily interact with the native structure of an
>>>>> external store. For option #2, a full identifier would be an
>>>>> arbitrary-length list of identifiers. For the example above, using
>>>>> catalog.schema.database.table is allowed. An identifier would be
>>>>> something like this:
>>>>>
>>>>> case class CatalogIdentifier(parts: Seq[String])
>>>>>
>>>>> The problem with option #2 is how to implement a listing and discovery
>>>>> API, for operations like SHOW TABLES. If the catalog API requires a list(ident:
>>>>> CatalogIdentifier), what does it return? There is no guarantee that
>>>>> the listed objects are tables and not nested namespaces. How would Spark
>>>>> handle arbitrary nesting that differs across catalogs?
>>>>>
>>>>> Hopefully, I’ve captured the design question well enough for a
>>>>> productive discussion. Thanks!
>>>>>
>>>>> rb
>>>>>
>>>>> --
>>>>>
>>>>> Ryan Blue
>>>>>
>>>>> Software Engineer
>>>>>
>>>>> Netflix
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>>
>>>>> Ryan Blue
>>>>>
>>>>> Software Engineer
>>>>>
>>>>> Netflix
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>>
>>>>> Ryan Blue
>>>>>
>>>>> Software Engineer
>>>>>
>>>>> Netflix
>>>>>
>>>>
>>>>
>>>> --
>>>> Ryan Blue
>>>> Software Engineer
>>>> Netflix
>>>>
>>>
>>>
>>> --
>>> [image:
>>> https://databricks.com/sparkaisummit/north-america?utm_source=email&utm_medium=signature]
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>>
>>
>
> --
> Ryan Blue
> Software Engineer
> Netflix
>
Re: [DISCUSS] Identifiers with multi-catalog support
Posted by Ryan Blue <rb...@netflix.com.INVALID>.
I haven’t had a chance to look into Xiao’s concerns yet, but I do want to
address what Wenchen brought up:
a user’s query may break if someone registers a catalog whose name is same
with a namespace in the default catalog
The question is what to do if “a” is both a catalog name and a namespace in
the default catalog. You’re right that adding a catalog could break queries.
The alternative, to assume that “a” is a namespace if there is a conflict,
makes this problem far more likely to occur. The reason is that users can
also create namespaces. If a query depends on catalog “a” and a user
creates the namespace “a” in the default catalog, then this also breaks the
existing query.
I think the right answer is to prefer “a” as a catalog because it is far
less likely that users will affect other jobs. The proposed way to
configure catalogs is to add configuration properties to Spark. That is
done globally by administrators and locally in user sessions. On the other
hand, when anyone creates a namespace/database, that is always a globally.
So it is far more likely that users will break each other adding a
namespace because users don’t add catalogs globally.
Does that make sense?
With the namespace abstraction, is DATABASE an alias of namespace?
I don’t see a downside to using DATABASE as an alias for NAMESPACE.
Currently, it implies a single identifier. We could enforce that in Spark,
but I see no reason for extra code to do it. Catalogs that support just one
level of namespacing will reject namespaces in namespaces or multi-level
namespaces anyway. No need to do it in Spark.
According to the doc Xiao mentioned before, it seems column name resolution
is also a hard problem and I’m wondering if the rule needs to be changed to
fit n-part name.
I agree it’s a problem we need to solve and document, probably with an
SPIP. I’m not sure that it requires a solution as part of this work, but I
intend to go through Xiao’s email carefully to determine whether we need to
do it as part of this.
On Wed, Jan 23, 2019 at 11:17 PM Wenchen Fan <cl...@gmail.com> wrote:
> Hi Ryan,
>
> Sorry to be late to this party. I think the n-part namespace is a good
> idea, thanks for bringing up this great proposal!
>
> Here I want to confirm several things to make sure we are on the same page.
>
> *1. table name resolution*
> According to the pseudo-code you provided, for table name `a.b.c`, we will
> first check if `a` is a registered catalog. If it is, then `b` is the
> namespace, `c` is the table name. If `a` is not a registered catalog, then
> `a.b` is the namespace to search in the default catalog, and `c` is the
> table name.
>
> This means, a user's query may break if someone registers a catalog whose
> name is same with a namespace in the default catalog. I'm not saying this
> is bad and unacceptable, I just want to bring it to your attention and see
> if we care about it.
>
> This ambiguity is new, as previously the table name can at most have 2
> parts. So when the table name only has one part, it must be a table under
> the default database. When the table name has 2 parts, it must be a table
> under the specified database.
>
>
> *2. namespace*
> With the namespace abstraction, is DATABASE an alias of namespace? And
> users can create a DATABASE under a DATABASE?
>
>
> *3. column name resolution*
> I think column name resolution is very different from table name
> resolution. When we resolve a column, we have a scope: we know which tables
> we can resolve the column from. According to the doc Xiao mentioned before,
> it seems column name resolution is also a hard problem and I'm wondering if
> the rule needs to be changed to fit n-part name.
>
> Thanks,
> Wenchen
>
>
> On Thu, Jan 24, 2019 at 1:59 PM Xiao Li <li...@databricks.com> wrote:
>
>> This is the PR I mentioned in the meeting:
>> https://github.com/apache/spark/pull/17185 This was merged into Spark
>> 2.4. Now, the traditional DBMS users can easily migrate their previous
>> queries to Spark SQL.
>>
>> *Attached doc* is the design doc by* Sunitha Kambhampati*, who is
>> working for IBM BigInsight, STC and then CODAIT. She did a great work! She
>> compared Spark with most mainstream DBMS and made Spark consistent with the
>> others.
>>
>> In the doc, Table B shows the challenges we might face. Basically, the
>> database name, catalog name, table name, and column name could be the same.
>> Different resolution rules could cause different plans and then Spark will
>> return results. Since this is very fundamental, we need to decide it in the
>> early stage and also clearly document in the public Spark SQL doc. Our end
>> users can follow it.
>>
>> To ensure we do not introduce the behavior changes, based on my
>> understanding, we ONLY can add the new cases after the existing cases
>> [which are mentioned in the doc], instead of introducing a brand-new
>> resolution rule.
>>
>> Also, if we consider the nested columns, the cases become more complex.*
>> DB Tsai* raised it offline with me multiple times about various issues
>> in nested column handling. His company heavily used the nested columns. I
>> think we should also consider nested fields in the resolution design;
>> otherwise, we could easily break the existing behaviors. Spark SQL does not
>> have enough test cases for nested columns, to be honest.
>>
>> Cheers,
>>
>> Xiao
>>
>> On Tue, Jan 22, 2019 at 12:24 PM Ryan Blue <rb...@netflix.com.invalid>
>> wrote:
>>
>>> Thanks for reviewing this! I'll create an SPIP doc and issue for it and
>>> call a vote.
>>>
>>> On Tue, Jan 22, 2019 at 11:41 AM Matt Cheah <mc...@palantir.com> wrote:
>>>
>>>> +1 for n-part namespace as proposed. Agree that a short SPIP would be
>>>> appropriate for this. Perhaps also a JIRA ticket?
>>>>
>>>>
>>>>
>>>> -Matt Cheah
>>>>
>>>>
>>>>
>>>> *From: *Felix Cheung <fe...@hotmail.com>
>>>> *Date: *Sunday, January 20, 2019 at 4:48 PM
>>>> *To: *"rblue@netflix.com" <rb...@netflix.com>, Spark Dev List <
>>>> dev@spark.apache.org>
>>>> *Subject: *Re: [DISCUSS] Identifiers with multi-catalog support
>>>>
>>>>
>>>>
>>>> +1 I like Ryan last mail. Thank you for putting it clearly (should be a
>>>> spec/SPIP!)
>>>>
>>>>
>>>>
>>>> I agree and understand the need for 3 part id. However I don’t think we
>>>> should make assumption that it must be or can only be as long as 3 parts.
>>>> Once the catalog is identified (ie. The first part), the catalog should be
>>>> responsible for resolving the namespace or schema etc. Agree also path is
>>>> good idea to add to support file-based variant. Should separator be
>>>> optional (perhaps in *space) to keep this extensible (it might not always
>>>> be ‘.’)
>>>>
>>>>
>>>>
>>>> Also this whole scheme will need to play nice with column identifier as
>>>> well.
>>>>
>>>>
>>>>
>>>>
>>>> ------------------------------
>>>>
>>>> *From:* Ryan Blue <rb...@netflix.com.invalid>
>>>> *Sent:* Thursday, January 17, 2019 11:38 AM
>>>> *To:* Spark Dev List
>>>> *Subject:* Re: [DISCUSS] Identifiers with multi-catalog support
>>>>
>>>>
>>>>
>>>> Any discussion on how Spark should manage identifiers when multiple
>>>> catalogs are supported?
>>>>
>>>>
>>>>
>>>> I know this is an area where a lot of people are interested in making
>>>> progress, and it is a blocker for both multi-catalog support and CTAS in
>>>> DSv2.
>>>>
>>>>
>>>>
>>>> On Sun, Jan 13, 2019 at 2:22 PM Ryan Blue <rb...@netflix.com> wrote:
>>>>
>>>> I think that the solution to this problem is to mix the two approaches
>>>> by supporting 3 identifier parts: catalog, namespace, and name, where
>>>> namespace can be an n-part identifier:
>>>>
>>>> type Namespace = Seq[String]
>>>>
>>>> case class CatalogIdentifier(space: Namespace, name: String)
>>>>
>>>> This allows catalogs to work with the hierarchy of the external store,
>>>> but the catalog API only requires a few discovery methods to list
>>>> namespaces and to list each type of object in a namespace.
>>>>
>>>> def listNamespaces(): Seq[Namespace]
>>>>
>>>> def listNamespaces(space: Namespace, prefix: String): Seq[Namespace]
>>>>
>>>> def listTables(space: Namespace): Seq[CatalogIdentifier]
>>>>
>>>> def listViews(space: Namespace): Seq[CatalogIdentifier]
>>>>
>>>> def listFunctions(space: Namespace): Seq[CatalogIdentifier]
>>>>
>>>> The methods to list tables, views, or functions, would only return
>>>> identifiers for the type queried, not namespaces or the other objects.
>>>>
>>>> The SQL parser would be updated so that identifiers are parsed to UnresovledIdentifier(parts:
>>>> Seq[String]), and resolution would work like this pseudo-code:
>>>>
>>>> def resolveIdentifier(ident: UnresolvedIdentifier): (CatalogPlugin, CatalogIdentifier) = {
>>>>
>>>> val maybeCatalog = sparkSession.catalog(ident.parts.head)
>>>>
>>>> ident.parts match {
>>>>
>>>> case Seq(catalogName, *space, name) if catalog.isDefined =>
>>>>
>>>> (maybeCatalog.get, CatalogIdentifier(space, name))
>>>>
>>>> case Seq(*space, name) =>
>>>>
>>>> (sparkSession.defaultCatalog, CatalogIdentifier(space, name))
>>>>
>>>> }
>>>>
>>>> }
>>>>
>>>> I think this is a good approach because it allows Spark users to
>>>> reference or discovery any name in the hierarchy of an external store, it
>>>> uses a few well-defined methods for discovery, and makes name hierarchy a
>>>> user concern.
>>>>
>>>> · SHOW (DATABASES|SCHEMAS|NAMESPACES) would return the result
>>>> of listNamespaces()
>>>>
>>>> · SHOW NAMESPACES LIKE a.b% would return the result of listNamespaces(Seq("a"),
>>>> "b")
>>>>
>>>> · USE a.b would set the current namespace to Seq("a", "b")
>>>>
>>>> · SHOW TABLES would return the result of
>>>> listTables(currentNamespace)
>>>>
>>>> Also, I think that we could generalize this a little more to support
>>>> path-based tables by adding a path to CatalogIdentifier, either as a
>>>> namespace or as a separate optional string. Then, the identifier passed to
>>>> a catalog would work for either a path-based table or a catalog table,
>>>> without needing a path-based catalog API.
>>>>
>>>> Thoughts?
>>>>
>>>>
>>>>
>>>> On Sun, Jan 13, 2019 at 1:38 PM Ryan Blue <rb...@netflix.com> wrote:
>>>>
>>>> In the DSv2 sync up, we tried to discuss the Table metadata proposal
>>>> but were side-tracked on its use of TableIdentifier. There were good points
>>>> about how Spark should identify tables, views, functions, etc, and I want
>>>> to start a discussion here.
>>>>
>>>> Identifiers are orthogonal to the TableCatalog proposal that can be
>>>> updated to use whatever identifier class we choose. That proposal is
>>>> concerned with what information should be passed to define a table, and how
>>>> to pass that information.
>>>>
>>>> The main question for *this* discussion is: *how should Spark identify
>>>> tables, views, and functions when it supports multiple catalogs?*
>>>>
>>>> There are two main approaches:
>>>>
>>>> 1. Use a 3-part identifier, catalog.database.table
>>>>
>>>> 2. Use an identifier with an arbitrary number of parts
>>>>
>>>> *Option 1: use 3-part identifiers*
>>>>
>>>> The argument for option #1 is that it is simple. If an external data
>>>> store has additional logical hierarchy layers, then that hierarchy would be
>>>> mapped to multiple catalogs in Spark. Spark can support show tables and
>>>> show databases without much trouble. This is the approach used by Presto,
>>>> so there is some precedent for it.
>>>>
>>>> The drawback is that mapping a more complex hierarchy into Spark
>>>> requires more configuration. If an external DB has a 3-level hierarchy —
>>>> say, for example, schema.database.table — then option #1 requires
>>>> users to configure a catalog for each top-level structure, each schema.
>>>> When a new schema is added, it is not automatically accessible.
>>>>
>>>> Catalog implementations could use session options could provide a rough
>>>> work-around by changing the plugin’s “current” schema. I think this is an
>>>> anti-pattern, so another strike against this option is that it encourages
>>>> bad practices.
>>>>
>>>> *Option 2: use n-part identifiers*
>>>>
>>>> That drawback for option #1 is the main argument for option #2: Spark
>>>> should allow users to easily interact with the native structure of an
>>>> external store. For option #2, a full identifier would be an
>>>> arbitrary-length list of identifiers. For the example above, using
>>>> catalog.schema.database.table is allowed. An identifier would be
>>>> something like this:
>>>>
>>>> case class CatalogIdentifier(parts: Seq[String])
>>>>
>>>> The problem with option #2 is how to implement a listing and discovery
>>>> API, for operations like SHOW TABLES. If the catalog API requires a list(ident:
>>>> CatalogIdentifier), what does it return? There is no guarantee that
>>>> the listed objects are tables and not nested namespaces. How would Spark
>>>> handle arbitrary nesting that differs across catalogs?
>>>>
>>>> Hopefully, I’ve captured the design question well enough for a
>>>> productive discussion. Thanks!
>>>>
>>>> rb
>>>>
>>>> --
>>>>
>>>> Ryan Blue
>>>>
>>>> Software Engineer
>>>>
>>>> Netflix
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>>
>>>> Ryan Blue
>>>>
>>>> Software Engineer
>>>>
>>>> Netflix
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>>
>>>> Ryan Blue
>>>>
>>>> Software Engineer
>>>>
>>>> Netflix
>>>>
>>>
>>>
>>> --
>>> Ryan Blue
>>> Software Engineer
>>> Netflix
>>>
>>
>>
>> --
>> [image:
>> https://databricks.com/sparkaisummit/north-america?utm_source=email&utm_medium=signature]
>>
>> ---------------------------------------------------------------------
>> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>
>
--
Ryan Blue
Software Engineer
Netflix
Re: [DISCUSS] Identifiers with multi-catalog support
Posted by Wenchen Fan <cl...@gmail.com>.
Hi Ryan,
Sorry to be late to this party. I think the n-part namespace is a good
idea, thanks for bringing up this great proposal!
Here I want to confirm several things to make sure we are on the same page.
*1. table name resolution*
According to the pseudo-code you provided, for table name `a.b.c`, we will
first check if `a` is a registered catalog. If it is, then `b` is the
namespace, `c` is the table name. If `a` is not a registered catalog, then
`a.b` is the namespace to search in the default catalog, and `c` is the
table name.
This means, a user's query may break if someone registers a catalog whose
name is same with a namespace in the default catalog. I'm not saying this
is bad and unacceptable, I just want to bring it to your attention and see
if we care about it.
This ambiguity is new, as previously the table name can at most have 2
parts. So when the table name only has one part, it must be a table under
the default database. When the table name has 2 parts, it must be a table
under the specified database.
*2. namespace*
With the namespace abstraction, is DATABASE an alias of namespace? And
users can create a DATABASE under a DATABASE?
*3. column name resolution*
I think column name resolution is very different from table name
resolution. When we resolve a column, we have a scope: we know which tables
we can resolve the column from. According to the doc Xiao mentioned before,
it seems column name resolution is also a hard problem and I'm wondering if
the rule needs to be changed to fit n-part name.
Thanks,
Wenchen
On Thu, Jan 24, 2019 at 1:59 PM Xiao Li <li...@databricks.com> wrote:
> This is the PR I mentioned in the meeting:
> https://github.com/apache/spark/pull/17185 This was merged into Spark
> 2.4. Now, the traditional DBMS users can easily migrate their previous
> queries to Spark SQL.
>
> *Attached doc* is the design doc by* Sunitha Kambhampati*, who is working
> for IBM BigInsight, STC and then CODAIT. She did a great work! She compared
> Spark with most mainstream DBMS and made Spark consistent with the others.
>
> In the doc, Table B shows the challenges we might face. Basically, the
> database name, catalog name, table name, and column name could be the same.
> Different resolution rules could cause different plans and then Spark will
> return results. Since this is very fundamental, we need to decide it in the
> early stage and also clearly document in the public Spark SQL doc. Our end
> users can follow it.
>
> To ensure we do not introduce the behavior changes, based on my
> understanding, we ONLY can add the new cases after the existing cases
> [which are mentioned in the doc], instead of introducing a brand-new
> resolution rule.
>
> Also, if we consider the nested columns, the cases become more complex.*
> DB Tsai* raised it offline with me multiple times about various issues in
> nested column handling. His company heavily used the nested columns. I
> think we should also consider nested fields in the resolution design;
> otherwise, we could easily break the existing behaviors. Spark SQL does not
> have enough test cases for nested columns, to be honest.
>
> Cheers,
>
> Xiao
>
> On Tue, Jan 22, 2019 at 12:24 PM Ryan Blue <rb...@netflix.com.invalid>
> wrote:
>
>> Thanks for reviewing this! I'll create an SPIP doc and issue for it and
>> call a vote.
>>
>> On Tue, Jan 22, 2019 at 11:41 AM Matt Cheah <mc...@palantir.com> wrote:
>>
>>> +1 for n-part namespace as proposed. Agree that a short SPIP would be
>>> appropriate for this. Perhaps also a JIRA ticket?
>>>
>>>
>>>
>>> -Matt Cheah
>>>
>>>
>>>
>>> *From: *Felix Cheung <fe...@hotmail.com>
>>> *Date: *Sunday, January 20, 2019 at 4:48 PM
>>> *To: *"rblue@netflix.com" <rb...@netflix.com>, Spark Dev List <
>>> dev@spark.apache.org>
>>> *Subject: *Re: [DISCUSS] Identifiers with multi-catalog support
>>>
>>>
>>>
>>> +1 I like Ryan last mail. Thank you for putting it clearly (should be a
>>> spec/SPIP!)
>>>
>>>
>>>
>>> I agree and understand the need for 3 part id. However I don’t think we
>>> should make assumption that it must be or can only be as long as 3 parts.
>>> Once the catalog is identified (ie. The first part), the catalog should be
>>> responsible for resolving the namespace or schema etc. Agree also path is
>>> good idea to add to support file-based variant. Should separator be
>>> optional (perhaps in *space) to keep this extensible (it might not always
>>> be ‘.’)
>>>
>>>
>>>
>>> Also this whole scheme will need to play nice with column identifier as
>>> well.
>>>
>>>
>>>
>>>
>>> ------------------------------
>>>
>>> *From:* Ryan Blue <rb...@netflix.com.invalid>
>>> *Sent:* Thursday, January 17, 2019 11:38 AM
>>> *To:* Spark Dev List
>>> *Subject:* Re: [DISCUSS] Identifiers with multi-catalog support
>>>
>>>
>>>
>>> Any discussion on how Spark should manage identifiers when multiple
>>> catalogs are supported?
>>>
>>>
>>>
>>> I know this is an area where a lot of people are interested in making
>>> progress, and it is a blocker for both multi-catalog support and CTAS in
>>> DSv2.
>>>
>>>
>>>
>>> On Sun, Jan 13, 2019 at 2:22 PM Ryan Blue <rb...@netflix.com> wrote:
>>>
>>> I think that the solution to this problem is to mix the two approaches
>>> by supporting 3 identifier parts: catalog, namespace, and name, where
>>> namespace can be an n-part identifier:
>>>
>>> type Namespace = Seq[String]
>>>
>>> case class CatalogIdentifier(space: Namespace, name: String)
>>>
>>> This allows catalogs to work with the hierarchy of the external store,
>>> but the catalog API only requires a few discovery methods to list
>>> namespaces and to list each type of object in a namespace.
>>>
>>> def listNamespaces(): Seq[Namespace]
>>>
>>> def listNamespaces(space: Namespace, prefix: String): Seq[Namespace]
>>>
>>> def listTables(space: Namespace): Seq[CatalogIdentifier]
>>>
>>> def listViews(space: Namespace): Seq[CatalogIdentifier]
>>>
>>> def listFunctions(space: Namespace): Seq[CatalogIdentifier]
>>>
>>> The methods to list tables, views, or functions, would only return
>>> identifiers for the type queried, not namespaces or the other objects.
>>>
>>> The SQL parser would be updated so that identifiers are parsed to UnresovledIdentifier(parts:
>>> Seq[String]), and resolution would work like this pseudo-code:
>>>
>>> def resolveIdentifier(ident: UnresolvedIdentifier): (CatalogPlugin, CatalogIdentifier) = {
>>>
>>> val maybeCatalog = sparkSession.catalog(ident.parts.head)
>>>
>>> ident.parts match {
>>>
>>> case Seq(catalogName, *space, name) if catalog.isDefined =>
>>>
>>> (maybeCatalog.get, CatalogIdentifier(space, name))
>>>
>>> case Seq(*space, name) =>
>>>
>>> (sparkSession.defaultCatalog, CatalogIdentifier(space, name))
>>>
>>> }
>>>
>>> }
>>>
>>> I think this is a good approach because it allows Spark users to
>>> reference or discovery any name in the hierarchy of an external store, it
>>> uses a few well-defined methods for discovery, and makes name hierarchy a
>>> user concern.
>>>
>>> · SHOW (DATABASES|SCHEMAS|NAMESPACES) would return the result
>>> of listNamespaces()
>>>
>>> · SHOW NAMESPACES LIKE a.b% would return the result of listNamespaces(Seq("a"),
>>> "b")
>>>
>>> · USE a.b would set the current namespace to Seq("a", "b")
>>>
>>> · SHOW TABLES would return the result of
>>> listTables(currentNamespace)
>>>
>>> Also, I think that we could generalize this a little more to support
>>> path-based tables by adding a path to CatalogIdentifier, either as a
>>> namespace or as a separate optional string. Then, the identifier passed to
>>> a catalog would work for either a path-based table or a catalog table,
>>> without needing a path-based catalog API.
>>>
>>> Thoughts?
>>>
>>>
>>>
>>> On Sun, Jan 13, 2019 at 1:38 PM Ryan Blue <rb...@netflix.com> wrote:
>>>
>>> In the DSv2 sync up, we tried to discuss the Table metadata proposal but
>>> were side-tracked on its use of TableIdentifier. There were good points
>>> about how Spark should identify tables, views, functions, etc, and I want
>>> to start a discussion here.
>>>
>>> Identifiers are orthogonal to the TableCatalog proposal that can be
>>> updated to use whatever identifier class we choose. That proposal is
>>> concerned with what information should be passed to define a table, and how
>>> to pass that information.
>>>
>>> The main question for *this* discussion is: *how should Spark identify
>>> tables, views, and functions when it supports multiple catalogs?*
>>>
>>> There are two main approaches:
>>>
>>> 1. Use a 3-part identifier, catalog.database.table
>>>
>>> 2. Use an identifier with an arbitrary number of parts
>>>
>>> *Option 1: use 3-part identifiers*
>>>
>>> The argument for option #1 is that it is simple. If an external data
>>> store has additional logical hierarchy layers, then that hierarchy would be
>>> mapped to multiple catalogs in Spark. Spark can support show tables and
>>> show databases without much trouble. This is the approach used by Presto,
>>> so there is some precedent for it.
>>>
>>> The drawback is that mapping a more complex hierarchy into Spark
>>> requires more configuration. If an external DB has a 3-level hierarchy —
>>> say, for example, schema.database.table — then option #1 requires users
>>> to configure a catalog for each top-level structure, each schema. When a
>>> new schema is added, it is not automatically accessible.
>>>
>>> Catalog implementations could use session options could provide a rough
>>> work-around by changing the plugin’s “current” schema. I think this is an
>>> anti-pattern, so another strike against this option is that it encourages
>>> bad practices.
>>>
>>> *Option 2: use n-part identifiers*
>>>
>>> That drawback for option #1 is the main argument for option #2: Spark
>>> should allow users to easily interact with the native structure of an
>>> external store. For option #2, a full identifier would be an
>>> arbitrary-length list of identifiers. For the example above, using
>>> catalog.schema.database.table is allowed. An identifier would be
>>> something like this:
>>>
>>> case class CatalogIdentifier(parts: Seq[String])
>>>
>>> The problem with option #2 is how to implement a listing and discovery
>>> API, for operations like SHOW TABLES. If the catalog API requires a list(ident:
>>> CatalogIdentifier), what does it return? There is no guarantee that the
>>> listed objects are tables and not nested namespaces. How would Spark handle
>>> arbitrary nesting that differs across catalogs?
>>>
>>> Hopefully, I’ve captured the design question well enough for a
>>> productive discussion. Thanks!
>>>
>>> rb
>>>
>>> --
>>>
>>> Ryan Blue
>>>
>>> Software Engineer
>>>
>>> Netflix
>>>
>>>
>>>
>>>
>>> --
>>>
>>> Ryan Blue
>>>
>>> Software Engineer
>>>
>>> Netflix
>>>
>>>
>>>
>>>
>>> --
>>>
>>> Ryan Blue
>>>
>>> Software Engineer
>>>
>>> Netflix
>>>
>>
>>
>> --
>> Ryan Blue
>> Software Engineer
>> Netflix
>>
>
>
> --
> [image:
> https://databricks.com/sparkaisummit/north-america?utm_source=email&utm_medium=signature]
>
> ---------------------------------------------------------------------
> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
Re: [DISCUSS] Identifiers with multi-catalog support
Posted by Xiao Li <li...@databricks.com>.
This is the PR I mentioned in the meeting:
https://github.com/apache/spark/pull/17185 This was merged into Spark 2.4.
Now, the traditional DBMS users can easily migrate their previous queries
to Spark SQL.
*Attached doc* is the design doc by* Sunitha Kambhampati*, who is working
for IBM BigInsight, STC and then CODAIT. She did a great work! She compared
Spark with most mainstream DBMS and made Spark consistent with the others.
In the doc, Table B shows the challenges we might face. Basically, the
database name, catalog name, table name, and column name could be the same.
Different resolution rules could cause different plans and then Spark will
return results. Since this is very fundamental, we need to decide it in the
early stage and also clearly document in the public Spark SQL doc. Our end
users can follow it.
To ensure we do not introduce the behavior changes, based on my
understanding, we ONLY can add the new cases after the existing cases
[which are mentioned in the doc], instead of introducing a brand-new
resolution rule.
Also, if we consider the nested columns, the cases become more complex.* DB
Tsai* raised it offline with me multiple times about various issues in
nested column handling. His company heavily used the nested columns. I
think we should also consider nested fields in the resolution design;
otherwise, we could easily break the existing behaviors. Spark SQL does not
have enough test cases for nested columns, to be honest.
Cheers,
Xiao
On Tue, Jan 22, 2019 at 12:24 PM Ryan Blue <rb...@netflix.com.invalid>
wrote:
> Thanks for reviewing this! I'll create an SPIP doc and issue for it and
> call a vote.
>
> On Tue, Jan 22, 2019 at 11:41 AM Matt Cheah <mc...@palantir.com> wrote:
>
>> +1 for n-part namespace as proposed. Agree that a short SPIP would be
>> appropriate for this. Perhaps also a JIRA ticket?
>>
>>
>>
>> -Matt Cheah
>>
>>
>>
>> *From: *Felix Cheung <fe...@hotmail.com>
>> *Date: *Sunday, January 20, 2019 at 4:48 PM
>> *To: *"rblue@netflix.com" <rb...@netflix.com>, Spark Dev List <
>> dev@spark.apache.org>
>> *Subject: *Re: [DISCUSS] Identifiers with multi-catalog support
>>
>>
>>
>> +1 I like Ryan last mail. Thank you for putting it clearly (should be a
>> spec/SPIP!)
>>
>>
>>
>> I agree and understand the need for 3 part id. However I don’t think we
>> should make assumption that it must be or can only be as long as 3 parts.
>> Once the catalog is identified (ie. The first part), the catalog should be
>> responsible for resolving the namespace or schema etc. Agree also path is
>> good idea to add to support file-based variant. Should separator be
>> optional (perhaps in *space) to keep this extensible (it might not always
>> be ‘.’)
>>
>>
>>
>> Also this whole scheme will need to play nice with column identifier as
>> well.
>>
>>
>>
>>
>> ------------------------------
>>
>> *From:* Ryan Blue <rb...@netflix.com.invalid>
>> *Sent:* Thursday, January 17, 2019 11:38 AM
>> *To:* Spark Dev List
>> *Subject:* Re: [DISCUSS] Identifiers with multi-catalog support
>>
>>
>>
>> Any discussion on how Spark should manage identifiers when multiple
>> catalogs are supported?
>>
>>
>>
>> I know this is an area where a lot of people are interested in making
>> progress, and it is a blocker for both multi-catalog support and CTAS in
>> DSv2.
>>
>>
>>
>> On Sun, Jan 13, 2019 at 2:22 PM Ryan Blue <rb...@netflix.com> wrote:
>>
>> I think that the solution to this problem is to mix the two approaches by
>> supporting 3 identifier parts: catalog, namespace, and name, where
>> namespace can be an n-part identifier:
>>
>> type Namespace = Seq[String]
>>
>> case class CatalogIdentifier(space: Namespace, name: String)
>>
>> This allows catalogs to work with the hierarchy of the external store,
>> but the catalog API only requires a few discovery methods to list
>> namespaces and to list each type of object in a namespace.
>>
>> def listNamespaces(): Seq[Namespace]
>>
>> def listNamespaces(space: Namespace, prefix: String): Seq[Namespace]
>>
>> def listTables(space: Namespace): Seq[CatalogIdentifier]
>>
>> def listViews(space: Namespace): Seq[CatalogIdentifier]
>>
>> def listFunctions(space: Namespace): Seq[CatalogIdentifier]
>>
>> The methods to list tables, views, or functions, would only return
>> identifiers for the type queried, not namespaces or the other objects.
>>
>> The SQL parser would be updated so that identifiers are parsed to UnresovledIdentifier(parts:
>> Seq[String]), and resolution would work like this pseudo-code:
>>
>> def resolveIdentifier(ident: UnresolvedIdentifier): (CatalogPlugin, CatalogIdentifier) = {
>>
>> val maybeCatalog = sparkSession.catalog(ident.parts.head)
>>
>> ident.parts match {
>>
>> case Seq(catalogName, *space, name) if catalog.isDefined =>
>>
>> (maybeCatalog.get, CatalogIdentifier(space, name))
>>
>> case Seq(*space, name) =>
>>
>> (sparkSession.defaultCatalog, CatalogIdentifier(space, name))
>>
>> }
>>
>> }
>>
>> I think this is a good approach because it allows Spark users to
>> reference or discovery any name in the hierarchy of an external store, it
>> uses a few well-defined methods for discovery, and makes name hierarchy a
>> user concern.
>>
>> · SHOW (DATABASES|SCHEMAS|NAMESPACES) would return the result of
>> listNamespaces()
>>
>> · SHOW NAMESPACES LIKE a.b% would return the result of listNamespaces(Seq("a"),
>> "b")
>>
>> · USE a.b would set the current namespace to Seq("a", "b")
>>
>> · SHOW TABLES would return the result of
>> listTables(currentNamespace)
>>
>> Also, I think that we could generalize this a little more to support
>> path-based tables by adding a path to CatalogIdentifier, either as a
>> namespace or as a separate optional string. Then, the identifier passed to
>> a catalog would work for either a path-based table or a catalog table,
>> without needing a path-based catalog API.
>>
>> Thoughts?
>>
>>
>>
>> On Sun, Jan 13, 2019 at 1:38 PM Ryan Blue <rb...@netflix.com> wrote:
>>
>> In the DSv2 sync up, we tried to discuss the Table metadata proposal but
>> were side-tracked on its use of TableIdentifier. There were good points
>> about how Spark should identify tables, views, functions, etc, and I want
>> to start a discussion here.
>>
>> Identifiers are orthogonal to the TableCatalog proposal that can be
>> updated to use whatever identifier class we choose. That proposal is
>> concerned with what information should be passed to define a table, and how
>> to pass that information.
>>
>> The main question for *this* discussion is: *how should Spark identify
>> tables, views, and functions when it supports multiple catalogs?*
>>
>> There are two main approaches:
>>
>> 1. Use a 3-part identifier, catalog.database.table
>>
>> 2. Use an identifier with an arbitrary number of parts
>>
>> *Option 1: use 3-part identifiers*
>>
>> The argument for option #1 is that it is simple. If an external data
>> store has additional logical hierarchy layers, then that hierarchy would be
>> mapped to multiple catalogs in Spark. Spark can support show tables and
>> show databases without much trouble. This is the approach used by Presto,
>> so there is some precedent for it.
>>
>> The drawback is that mapping a more complex hierarchy into Spark requires
>> more configuration. If an external DB has a 3-level hierarchy — say, for
>> example, schema.database.table — then option #1 requires users to
>> configure a catalog for each top-level structure, each schema. When a new
>> schema is added, it is not automatically accessible.
>>
>> Catalog implementations could use session options could provide a rough
>> work-around by changing the plugin’s “current” schema. I think this is an
>> anti-pattern, so another strike against this option is that it encourages
>> bad practices.
>>
>> *Option 2: use n-part identifiers*
>>
>> That drawback for option #1 is the main argument for option #2: Spark
>> should allow users to easily interact with the native structure of an
>> external store. For option #2, a full identifier would be an
>> arbitrary-length list of identifiers. For the example above, using
>> catalog.schema.database.table is allowed. An identifier would be
>> something like this:
>>
>> case class CatalogIdentifier(parts: Seq[String])
>>
>> The problem with option #2 is how to implement a listing and discovery
>> API, for operations like SHOW TABLES. If the catalog API requires a list(ident:
>> CatalogIdentifier), what does it return? There is no guarantee that the
>> listed objects are tables and not nested namespaces. How would Spark handle
>> arbitrary nesting that differs across catalogs?
>>
>> Hopefully, I’ve captured the design question well enough for a productive
>> discussion. Thanks!
>>
>> rb
>>
>> --
>>
>> Ryan Blue
>>
>> Software Engineer
>>
>> Netflix
>>
>>
>>
>>
>> --
>>
>> Ryan Blue
>>
>> Software Engineer
>>
>> Netflix
>>
>>
>>
>>
>> --
>>
>> Ryan Blue
>>
>> Software Engineer
>>
>> Netflix
>>
>
>
> --
> Ryan Blue
> Software Engineer
> Netflix
>
--
[image:
https://databricks.com/sparkaisummit/north-america?utm_source=email&utm_medium=signature]
Re: [DISCUSS] Identifiers with multi-catalog support
Posted by Ryan Blue <rb...@netflix.com.INVALID>.
Thanks for reviewing this! I'll create an SPIP doc and issue for it and
call a vote.
On Tue, Jan 22, 2019 at 11:41 AM Matt Cheah <mc...@palantir.com> wrote:
> +1 for n-part namespace as proposed. Agree that a short SPIP would be
> appropriate for this. Perhaps also a JIRA ticket?
>
>
>
> -Matt Cheah
>
>
>
> *From: *Felix Cheung <fe...@hotmail.com>
> *Date: *Sunday, January 20, 2019 at 4:48 PM
> *To: *"rblue@netflix.com" <rb...@netflix.com>, Spark Dev List <
> dev@spark.apache.org>
> *Subject: *Re: [DISCUSS] Identifiers with multi-catalog support
>
>
>
> +1 I like Ryan last mail. Thank you for putting it clearly (should be a
> spec/SPIP!)
>
>
>
> I agree and understand the need for 3 part id. However I don’t think we
> should make assumption that it must be or can only be as long as 3 parts.
> Once the catalog is identified (ie. The first part), the catalog should be
> responsible for resolving the namespace or schema etc. Agree also path is
> good idea to add to support file-based variant. Should separator be
> optional (perhaps in *space) to keep this extensible (it might not always
> be ‘.’)
>
>
>
> Also this whole scheme will need to play nice with column identifier as
> well.
>
>
>
>
> ------------------------------
>
> *From:* Ryan Blue <rb...@netflix.com.invalid>
> *Sent:* Thursday, January 17, 2019 11:38 AM
> *To:* Spark Dev List
> *Subject:* Re: [DISCUSS] Identifiers with multi-catalog support
>
>
>
> Any discussion on how Spark should manage identifiers when multiple
> catalogs are supported?
>
>
>
> I know this is an area where a lot of people are interested in making
> progress, and it is a blocker for both multi-catalog support and CTAS in
> DSv2.
>
>
>
> On Sun, Jan 13, 2019 at 2:22 PM Ryan Blue <rb...@netflix.com> wrote:
>
> I think that the solution to this problem is to mix the two approaches by
> supporting 3 identifier parts: catalog, namespace, and name, where
> namespace can be an n-part identifier:
>
> type Namespace = Seq[String]
>
> case class CatalogIdentifier(space: Namespace, name: String)
>
> This allows catalogs to work with the hierarchy of the external store, but
> the catalog API only requires a few discovery methods to list namespaces
> and to list each type of object in a namespace.
>
> def listNamespaces(): Seq[Namespace]
>
> def listNamespaces(space: Namespace, prefix: String): Seq[Namespace]
>
> def listTables(space: Namespace): Seq[CatalogIdentifier]
>
> def listViews(space: Namespace): Seq[CatalogIdentifier]
>
> def listFunctions(space: Namespace): Seq[CatalogIdentifier]
>
> The methods to list tables, views, or functions, would only return
> identifiers for the type queried, not namespaces or the other objects.
>
> The SQL parser would be updated so that identifiers are parsed to UnresovledIdentifier(parts:
> Seq[String]), and resolution would work like this pseudo-code:
>
> def resolveIdentifier(ident: UnresolvedIdentifier): (CatalogPlugin, CatalogIdentifier) = {
>
> val maybeCatalog = sparkSession.catalog(ident.parts.head)
>
> ident.parts match {
>
> case Seq(catalogName, *space, name) if catalog.isDefined =>
>
> (maybeCatalog.get, CatalogIdentifier(space, name))
>
> case Seq(*space, name) =>
>
> (sparkSession.defaultCatalog, CatalogIdentifier(space, name))
>
> }
>
> }
>
> I think this is a good approach because it allows Spark users to reference
> or discovery any name in the hierarchy of an external store, it uses a few
> well-defined methods for discovery, and makes name hierarchy a user concern.
>
> · SHOW (DATABASES|SCHEMAS|NAMESPACES) would return the result of
> listNamespaces()
>
> · SHOW NAMESPACES LIKE a.b% would return the result of listNamespaces(Seq("a"),
> "b")
>
> · USE a.b would set the current namespace to Seq("a", "b")
>
> · SHOW TABLES would return the result of
> listTables(currentNamespace)
>
> Also, I think that we could generalize this a little more to support
> path-based tables by adding a path to CatalogIdentifier, either as a
> namespace or as a separate optional string. Then, the identifier passed to
> a catalog would work for either a path-based table or a catalog table,
> without needing a path-based catalog API.
>
> Thoughts?
>
>
>
> On Sun, Jan 13, 2019 at 1:38 PM Ryan Blue <rb...@netflix.com> wrote:
>
> In the DSv2 sync up, we tried to discuss the Table metadata proposal but
> were side-tracked on its use of TableIdentifier. There were good points
> about how Spark should identify tables, views, functions, etc, and I want
> to start a discussion here.
>
> Identifiers are orthogonal to the TableCatalog proposal that can be
> updated to use whatever identifier class we choose. That proposal is
> concerned with what information should be passed to define a table, and how
> to pass that information.
>
> The main question for *this* discussion is: *how should Spark identify
> tables, views, and functions when it supports multiple catalogs?*
>
> There are two main approaches:
>
> 1. Use a 3-part identifier, catalog.database.table
>
> 2. Use an identifier with an arbitrary number of parts
>
> *Option 1: use 3-part identifiers*
>
> The argument for option #1 is that it is simple. If an external data store
> has additional logical hierarchy layers, then that hierarchy would be
> mapped to multiple catalogs in Spark. Spark can support show tables and
> show databases without much trouble. This is the approach used by Presto,
> so there is some precedent for it.
>
> The drawback is that mapping a more complex hierarchy into Spark requires
> more configuration. If an external DB has a 3-level hierarchy — say, for
> example, schema.database.table — then option #1 requires users to
> configure a catalog for each top-level structure, each schema. When a new
> schema is added, it is not automatically accessible.
>
> Catalog implementations could use session options could provide a rough
> work-around by changing the plugin’s “current” schema. I think this is an
> anti-pattern, so another strike against this option is that it encourages
> bad practices.
>
> *Option 2: use n-part identifiers*
>
> That drawback for option #1 is the main argument for option #2: Spark
> should allow users to easily interact with the native structure of an
> external store. For option #2, a full identifier would be an
> arbitrary-length list of identifiers. For the example above, using
> catalog.schema.database.table is allowed. An identifier would be
> something like this:
>
> case class CatalogIdentifier(parts: Seq[String])
>
> The problem with option #2 is how to implement a listing and discovery
> API, for operations like SHOW TABLES. If the catalog API requires a list(ident:
> CatalogIdentifier), what does it return? There is no guarantee that the
> listed objects are tables and not nested namespaces. How would Spark handle
> arbitrary nesting that differs across catalogs?
>
> Hopefully, I’ve captured the design question well enough for a productive
> discussion. Thanks!
>
> rb
>
> --
>
> Ryan Blue
>
> Software Engineer
>
> Netflix
>
>
>
>
> --
>
> Ryan Blue
>
> Software Engineer
>
> Netflix
>
>
>
>
> --
>
> Ryan Blue
>
> Software Engineer
>
> Netflix
>
--
Ryan Blue
Software Engineer
Netflix
Re: [DISCUSS] Identifiers with multi-catalog support
Posted by Matt Cheah <mc...@palantir.com>.
+1 for n-part namespace as proposed. Agree that a short SPIP would be appropriate for this. Perhaps also a JIRA ticket?
-Matt Cheah
From: Felix Cheung <fe...@hotmail.com>
Date: Sunday, January 20, 2019 at 4:48 PM
To: "rblue@netflix.com" <rb...@netflix.com>, Spark Dev List <de...@spark.apache.org>
Subject: Re: [DISCUSS] Identifiers with multi-catalog support
+1 I like Ryan last mail. Thank you for putting it clearly (should be a spec/SPIP!)
I agree and understand the need for 3 part id. However I don’t think we should make assumption that it must be or can only be as long as 3 parts. Once the catalog is identified (ie. The first part), the catalog should be responsible for resolving the namespace or schema etc. Agree also path is good idea to add to support file-based variant. Should separator be optional (perhaps in *space) to keep this extensible (it might not always be ‘.’)
Also this whole scheme will need to play nice with column identifier as well.
From: Ryan Blue <rb...@netflix.com.invalid>
Sent: Thursday, January 17, 2019 11:38 AM
To: Spark Dev List
Subject: Re: [DISCUSS] Identifiers with multi-catalog support
Any discussion on how Spark should manage identifiers when multiple catalogs are supported?
I know this is an area where a lot of people are interested in making progress, and it is a blocker for both multi-catalog support and CTAS in DSv2.
On Sun, Jan 13, 2019 at 2:22 PM Ryan Blue <rb...@netflix.com> wrote:
I think that the solution to this problem is to mix the two approaches by supporting 3 identifier parts: catalog, namespace, and name, where namespace can be an n-part identifier:
type Namespace = Seq[String]
case class CatalogIdentifier(space: Namespace, name: String)
This allows catalogs to work with the hierarchy of the external store, but the catalog API only requires a few discovery methods to list namespaces and to list each type of object in a namespace.
def listNamespaces(): Seq[Namespace]
def listNamespaces(space: Namespace, prefix: String): Seq[Namespace]
def listTables(space: Namespace): Seq[CatalogIdentifier]
def listViews(space: Namespace): Seq[CatalogIdentifier]
def listFunctions(space: Namespace): Seq[CatalogIdentifier]
The methods to list tables, views, or functions, would only return identifiers for the type queried, not namespaces or the other objects.
The SQL parser would be updated so that identifiers are parsed to UnresovledIdentifier(parts: Seq[String]), and resolution would work like this pseudo-code:
def resolveIdentifier(ident: UnresolvedIdentifier): (CatalogPlugin, CatalogIdentifier) = {
val maybeCatalog = sparkSession.catalog(ident.parts.head)
ident.parts match {
case Seq(catalogName, *space, name) if catalog.isDefined =>
(maybeCatalog.get, CatalogIdentifier(space, name))
case Seq(*space, name) =>
(sparkSession.defaultCatalog, CatalogIdentifier(space, name))
}
}
I think this is a good approach because it allows Spark users to reference or discovery any name in the hierarchy of an external store, it uses a few well-defined methods for discovery, and makes name hierarchy a user concern.
· SHOW (DATABASES|SCHEMAS|NAMESPACES) would return the result of listNamespaces()
· SHOW NAMESPACES LIKE a.b% would return the result of listNamespaces(Seq("a"), "b")
· USE a.b would set the current namespace to Seq("a", "b")
· SHOW TABLES would return the result of listTables(currentNamespace)
Also, I think that we could generalize this a little more to support path-based tables by adding a path to CatalogIdentifier, either as a namespace or as a separate optional string. Then, the identifier passed to a catalog would work for either a path-based table or a catalog table, without needing a path-based catalog API.
Thoughts?
On Sun, Jan 13, 2019 at 1:38 PM Ryan Blue <rb...@netflix.com> wrote:
In the DSv2 sync up, we tried to discuss the Table metadata proposal but were side-tracked on its use of TableIdentifier. There were good points about how Spark should identify tables, views, functions, etc, and I want to start a discussion here.
Identifiers are orthogonal to the TableCatalog proposal that can be updated to use whatever identifier class we choose. That proposal is concerned with what information should be passed to define a table, and how to pass that information.
The main question for this discussion is: how should Spark identify tables, views, and functions when it supports multiple catalogs?
There are two main approaches:
1. Use a 3-part identifier, catalog.database.table
2. Use an identifier with an arbitrary number of parts
Option 1: use 3-part identifiers
The argument for option #1 is that it is simple. If an external data store has additional logical hierarchy layers, then that hierarchy would be mapped to multiple catalogs in Spark. Spark can support show tables and show databases without much trouble. This is the approach used by Presto, so there is some precedent for it.
The drawback is that mapping a more complex hierarchy into Spark requires more configuration. If an external DB has a 3-level hierarchy — say, for example, schema.database.table — then option #1 requires users to configure a catalog for each top-level structure, each schema. When a new schema is added, it is not automatically accessible.
Catalog implementations could use session options could provide a rough work-around by changing the plugin’s “current” schema. I think this is an anti-pattern, so another strike against this option is that it encourages bad practices.
Option 2: use n-part identifiers
That drawback for option #1 is the main argument for option #2: Spark should allow users to easily interact with the native structure of an external store. For option #2, a full identifier would be an arbitrary-length list of identifiers. For the example above, using catalog.schema.database.table is allowed. An identifier would be something like this:
case class CatalogIdentifier(parts: Seq[String])
The problem with option #2 is how to implement a listing and discovery API, for operations like SHOW TABLES. If the catalog API requires a list(ident: CatalogIdentifier), what does it return? There is no guarantee that the listed objects are tables and not nested namespaces. How would Spark handle arbitrary nesting that differs across catalogs?
Hopefully, I’ve captured the design question well enough for a productive discussion. Thanks!
rb
--
Ryan Blue
Software Engineer
Netflix
--
Ryan Blue
Software Engineer
Netflix
--
Ryan Blue
Software Engineer
Netflix
Re: [DISCUSS] Identifiers with multi-catalog support
Posted by Felix Cheung <fe...@hotmail.com>.
+1 I like Ryan last mail. Thank you for putting it clearly (should be a spec/SPIP!)
I agree and understand the need for 3 part id. However I don’t think we should make assumption that it must be or can only be as long as 3 parts. Once the catalog is identified (ie. The first part), the catalog should be responsible for resolving the namespace or schema etc. Agree also path is good idea to add to support file-based variant. Should separator be optional (perhaps in *space) to keep this extensible (it might not always be ‘.’)
Also this whole scheme will need to play nice with column identifier as well.
________________________________
From: Ryan Blue <rb...@netflix.com.invalid>
Sent: Thursday, January 17, 2019 11:38 AM
To: Spark Dev List
Subject: Re: [DISCUSS] Identifiers with multi-catalog support
Any discussion on how Spark should manage identifiers when multiple catalogs are supported?
I know this is an area where a lot of people are interested in making progress, and it is a blocker for both multi-catalog support and CTAS in DSv2.
On Sun, Jan 13, 2019 at 2:22 PM Ryan Blue <rb...@netflix.com>> wrote:
I think that the solution to this problem is to mix the two approaches by supporting 3 identifier parts: catalog, namespace, and name, where namespace can be an n-part identifier:
type Namespace = Seq[String]
case class CatalogIdentifier(space: Namespace, name: String)
This allows catalogs to work with the hierarchy of the external store, but the catalog API only requires a few discovery methods to list namespaces and to list each type of object in a namespace.
def listNamespaces(): Seq[Namespace]
def listNamespaces(space: Namespace, prefix: String): Seq[Namespace]
def listTables(space: Namespace): Seq[CatalogIdentifier]
def listViews(space: Namespace): Seq[CatalogIdentifier]
def listFunctions(space: Namespace): Seq[CatalogIdentifier]
The methods to list tables, views, or functions, would only return identifiers for the type queried, not namespaces or the other objects.
The SQL parser would be updated so that identifiers are parsed to UnresovledIdentifier(parts: Seq[String]), and resolution would work like this pseudo-code:
def resolveIdentifier(ident: UnresolvedIdentifier): (CatalogPlugin, CatalogIdentifier) = {
val maybeCatalog = sparkSession.catalog(ident.parts.head)
ident.parts match {
case Seq(catalogName, *space, name) if catalog.isDefined =>
(maybeCatalog.get, CatalogIdentifier(space, name))
case Seq(*space, name) =>
(sparkSession.defaultCatalog, CatalogIdentifier(space, name))
}
}
I think this is a good approach because it allows Spark users to reference or discovery any name in the hierarchy of an external store, it uses a few well-defined methods for discovery, and makes name hierarchy a user concern.
* SHOW (DATABASES|SCHEMAS|NAMESPACES) would return the result of listNamespaces()
* SHOW NAMESPACES LIKE a.b% would return the result of listNamespaces(Seq("a"), "b")
* USE a.b would set the current namespace to Seq("a", "b")
* SHOW TABLES would return the result of listTables(currentNamespace)
Also, I think that we could generalize this a little more to support path-based tables by adding a path to CatalogIdentifier, either as a namespace or as a separate optional string. Then, the identifier passed to a catalog would work for either a path-based table or a catalog table, without needing a path-based catalog API.
Thoughts?
On Sun, Jan 13, 2019 at 1:38 PM Ryan Blue <rb...@netflix.com>> wrote:
In the DSv2 sync up, we tried to discuss the Table metadata proposal but were side-tracked on its use of TableIdentifier. There were good points about how Spark should identify tables, views, functions, etc, and I want to start a discussion here.
Identifiers are orthogonal to the TableCatalog proposal that can be updated to use whatever identifier class we choose. That proposal is concerned with what information should be passed to define a table, and how to pass that information.
The main question for this discussion is: how should Spark identify tables, views, and functions when it supports multiple catalogs?
There are two main approaches:
1. Use a 3-part identifier, catalog.database.table
2. Use an identifier with an arbitrary number of parts
Option 1: use 3-part identifiers
The argument for option #1 is that it is simple. If an external data store has additional logical hierarchy layers, then that hierarchy would be mapped to multiple catalogs in Spark. Spark can support show tables and show databases without much trouble. This is the approach used by Presto, so there is some precedent for it.
The drawback is that mapping a more complex hierarchy into Spark requires more configuration. If an external DB has a 3-level hierarchy — say, for example, schema.database.table — then option #1 requires users to configure a catalog for each top-level structure, each schema. When a new schema is added, it is not automatically accessible.
Catalog implementations could use session options could provide a rough work-around by changing the plugin’s “current” schema. I think this is an anti-pattern, so another strike against this option is that it encourages bad practices.
Option 2: use n-part identifiers
That drawback for option #1 is the main argument for option #2: Spark should allow users to easily interact with the native structure of an external store. For option #2, a full identifier would be an arbitrary-length list of identifiers. For the example above, using catalog.schema.database.table is allowed. An identifier would be something like this:
case class CatalogIdentifier(parts: Seq[String])
The problem with option #2 is how to implement a listing and discovery API, for operations like SHOW TABLES. If the catalog API requires a list(ident: CatalogIdentifier), what does it return? There is no guarantee that the listed objects are tables and not nested namespaces. How would Spark handle arbitrary nesting that differs across catalogs?
Hopefully, I’ve captured the design question well enough for a productive discussion. Thanks!
rb
--
Ryan Blue
Software Engineer
Netflix
--
Ryan Blue
Software Engineer
Netflix
--
Ryan Blue
Software Engineer
Netflix
Re: [DISCUSS] Identifiers with multi-catalog support
Posted by Ryan Blue <rb...@netflix.com.INVALID>.
Any discussion on how Spark should manage identifiers when multiple
catalogs are supported?
I know this is an area where a lot of people are interested in making
progress, and it is a blocker for both multi-catalog support and CTAS in
DSv2.
On Sun, Jan 13, 2019 at 2:22 PM Ryan Blue <rb...@netflix.com> wrote:
> I think that the solution to this problem is to mix the two approaches by
> supporting 3 identifier parts: catalog, namespace, and name, where
> namespace can be an n-part identifier:
>
> type Namespace = Seq[String]
> case class CatalogIdentifier(space: Namespace, name: String)
>
> This allows catalogs to work with the hierarchy of the external store, but
> the catalog API only requires a few discovery methods to list namespaces
> and to list each type of object in a namespace.
>
> def listNamespaces(): Seq[Namespace]
> def listNamespaces(space: Namespace, prefix: String): Seq[Namespace]
> def listTables(space: Namespace): Seq[CatalogIdentifier]
> def listViews(space: Namespace): Seq[CatalogIdentifier]
> def listFunctions(space: Namespace): Seq[CatalogIdentifier]
>
> The methods to list tables, views, or functions, would only return
> identifiers for the type queried, not namespaces or the other objects.
>
> The SQL parser would be updated so that identifiers are parsed to UnresovledIdentifier(parts:
> Seq[String]), and resolution would work like this pseudo-code:
>
> def resolveIdentifier(ident: UnresolvedIdentifier): (CatalogPlugin, CatalogIdentifier) = {
> val maybeCatalog = sparkSession.catalog(ident.parts.head)
> ident.parts match {
> case Seq(catalogName, *space, name) if catalog.isDefined =>
> (maybeCatalog.get, CatalogIdentifier(space, name))
> case Seq(*space, name) =>
> (sparkSession.defaultCatalog, CatalogIdentifier(space, name))
> }
> }
>
> I think this is a good approach because it allows Spark users to reference
> or discovery any name in the hierarchy of an external store, it uses a few
> well-defined methods for discovery, and makes name hierarchy a user concern.
>
> - SHOW (DATABASES|SCHEMAS|NAMESPACES) would return the result of
> listNamespaces()
> - SHOW NAMESPACES LIKE a.b% would return the result of listNamespaces(Seq("a"),
> "b")
> - USE a.b would set the current namespace to Seq("a", "b")
> - SHOW TABLES would return the result of listTables(currentNamespace)
>
> Also, I think that we could generalize this a little more to support
> path-based tables by adding a path to CatalogIdentifier, either as a
> namespace or as a separate optional string. Then, the identifier passed to
> a catalog would work for either a path-based table or a catalog table,
> without needing a path-based catalog API.
>
> Thoughts?
>
> On Sun, Jan 13, 2019 at 1:38 PM Ryan Blue <rb...@netflix.com> wrote:
>
>> In the DSv2 sync up, we tried to discuss the Table metadata proposal but
>> were side-tracked on its use of TableIdentifier. There were good points
>> about how Spark should identify tables, views, functions, etc, and I want
>> to start a discussion here.
>>
>> Identifiers are orthogonal to the TableCatalog proposal that can be
>> updated to use whatever identifier class we choose. That proposal is
>> concerned with what information should be passed to define a table, and how
>> to pass that information.
>>
>> The main question for *this* discussion is: *how should Spark identify
>> tables, views, and functions when it supports multiple catalogs?*
>>
>> There are two main approaches:
>>
>> 1. Use a 3-part identifier, catalog.database.table
>> 2. Use an identifier with an arbitrary number of parts
>>
>> *Option 1: use 3-part identifiers*
>>
>> The argument for option #1 is that it is simple. If an external data
>> store has additional logical hierarchy layers, then that hierarchy would be
>> mapped to multiple catalogs in Spark. Spark can support show tables and
>> show databases without much trouble. This is the approach used by Presto,
>> so there is some precedent for it.
>>
>> The drawback is that mapping a more complex hierarchy into Spark requires
>> more configuration. If an external DB has a 3-level hierarchy — say, for
>> example, schema.database.table — then option #1 requires users to
>> configure a catalog for each top-level structure, each schema. When a new
>> schema is added, it is not automatically accessible.
>>
>> Catalog implementations could use session options could provide a rough
>> work-around by changing the plugin’s “current” schema. I think this is an
>> anti-pattern, so another strike against this option is that it encourages
>> bad practices.
>>
>> *Option 2: use n-part identifiers*
>>
>> That drawback for option #1 is the main argument for option #2: Spark
>> should allow users to easily interact with the native structure of an
>> external store. For option #2, a full identifier would be an
>> arbitrary-length list of identifiers. For the example above, using
>> catalog.schema.database.table is allowed. An identifier would be
>> something like this:
>>
>> case class CatalogIdentifier(parts: Seq[String])
>>
>> The problem with option #2 is how to implement a listing and discovery
>> API, for operations like SHOW TABLES. If the catalog API requires a list(ident:
>> CatalogIdentifier), what does it return? There is no guarantee that the
>> listed objects are tables and not nested namespaces. How would Spark handle
>> arbitrary nesting that differs across catalogs?
>>
>> Hopefully, I’ve captured the design question well enough for a productive
>> discussion. Thanks!
>>
>> rb
>> --
>> Ryan Blue
>> Software Engineer
>> Netflix
>>
>
>
> --
> Ryan Blue
> Software Engineer
> Netflix
>
--
Ryan Blue
Software Engineer
Netflix
Re: [DISCUSS] Identifiers with multi-catalog support
Posted by Ryan Blue <rb...@netflix.com.INVALID>.
I think that the solution to this problem is to mix the two approaches by
supporting 3 identifier parts: catalog, namespace, and name, where
namespace can be an n-part identifier:
type Namespace = Seq[String]
case class CatalogIdentifier(space: Namespace, name: String)
This allows catalogs to work with the hierarchy of the external store, but
the catalog API only requires a few discovery methods to list namespaces
and to list each type of object in a namespace.
def listNamespaces(): Seq[Namespace]
def listNamespaces(space: Namespace, prefix: String): Seq[Namespace]
def listTables(space: Namespace): Seq[CatalogIdentifier]
def listViews(space: Namespace): Seq[CatalogIdentifier]
def listFunctions(space: Namespace): Seq[CatalogIdentifier]
The methods to list tables, views, or functions, would only return
identifiers for the type queried, not namespaces or the other objects.
The SQL parser would be updated so that identifiers are parsed to
UnresovledIdentifier(parts:
Seq[String]), and resolution would work like this pseudo-code:
def resolveIdentifier(ident: UnresolvedIdentifier): (CatalogPlugin,
CatalogIdentifier) = {
val maybeCatalog = sparkSession.catalog(ident.parts.head)
ident.parts match {
case Seq(catalogName, *space, name) if catalog.isDefined =>
(maybeCatalog.get, CatalogIdentifier(space, name))
case Seq(*space, name) =>
(sparkSession.defaultCatalog, CatalogIdentifier(space, name))
}
}
I think this is a good approach because it allows Spark users to reference
or discovery any name in the hierarchy of an external store, it uses a few
well-defined methods for discovery, and makes name hierarchy a user concern.
- SHOW (DATABASES|SCHEMAS|NAMESPACES) would return the result of
listNamespaces()
- SHOW NAMESPACES LIKE a.b% would return the result of
listNamespaces(Seq("a"),
"b")
- USE a.b would set the current namespace to Seq("a", "b")
- SHOW TABLES would return the result of listTables(currentNamespace)
Also, I think that we could generalize this a little more to support
path-based tables by adding a path to CatalogIdentifier, either as a
namespace or as a separate optional string. Then, the identifier passed to
a catalog would work for either a path-based table or a catalog table,
without needing a path-based catalog API.
Thoughts?
On Sun, Jan 13, 2019 at 1:38 PM Ryan Blue <rb...@netflix.com> wrote:
> In the DSv2 sync up, we tried to discuss the Table metadata proposal but
> were side-tracked on its use of TableIdentifier. There were good points
> about how Spark should identify tables, views, functions, etc, and I want
> to start a discussion here.
>
> Identifiers are orthogonal to the TableCatalog proposal that can be
> updated to use whatever identifier class we choose. That proposal is
> concerned with what information should be passed to define a table, and how
> to pass that information.
>
> The main question for *this* discussion is: *how should Spark identify
> tables, views, and functions when it supports multiple catalogs?*
>
> There are two main approaches:
>
> 1. Use a 3-part identifier, catalog.database.table
> 2. Use an identifier with an arbitrary number of parts
>
> *Option 1: use 3-part identifiers*
>
> The argument for option #1 is that it is simple. If an external data store
> has additional logical hierarchy layers, then that hierarchy would be
> mapped to multiple catalogs in Spark. Spark can support show tables and
> show databases without much trouble. This is the approach used by Presto,
> so there is some precedent for it.
>
> The drawback is that mapping a more complex hierarchy into Spark requires
> more configuration. If an external DB has a 3-level hierarchy — say, for
> example, schema.database.table — then option #1 requires users to
> configure a catalog for each top-level structure, each schema. When a new
> schema is added, it is not automatically accessible.
>
> Catalog implementations could use session options could provide a rough
> work-around by changing the plugin’s “current” schema. I think this is an
> anti-pattern, so another strike against this option is that it encourages
> bad practices.
>
> *Option 2: use n-part identifiers*
>
> That drawback for option #1 is the main argument for option #2: Spark
> should allow users to easily interact with the native structure of an
> external store. For option #2, a full identifier would be an
> arbitrary-length list of identifiers. For the example above, using
> catalog.schema.database.table is allowed. An identifier would be
> something like this:
>
> case class CatalogIdentifier(parts: Seq[String])
>
> The problem with option #2 is how to implement a listing and discovery
> API, for operations like SHOW TABLES. If the catalog API requires a list(ident:
> CatalogIdentifier), what does it return? There is no guarantee that the
> listed objects are tables and not nested namespaces. How would Spark handle
> arbitrary nesting that differs across catalogs?
>
> Hopefully, I’ve captured the design question well enough for a productive
> discussion. Thanks!
>
> rb
> --
> Ryan Blue
> Software Engineer
> Netflix
>
--
Ryan Blue
Software Engineer
Netflix
Re: [DISCUSS] Identifiers with multi-catalog support
Posted by Reynold Xin <rx...@databricks.com>.
Thanks for writing this up. Just to show why option 1 is not sufficient. MySQL and Postgres are the two most popular open source database systems, and both support database → schema → table 3 part identification, so Spark supporting only 2 part name passing to the data source (option 1) isn't sufficient.
For the issues you brought up w.r.t. nesting - what's the challenge in supporting it? I can also see us not supporting it for now (no nesting allowed, leaf - 1 level can only contain leaf tables), and adding support for nesting in the future.
On Sun, Jan 13, 2019 at 1:38 PM, Ryan Blue < rblue@netflix.com.invalid > wrote:
>
>
>
> In the DSv2 sync up, we tried to discuss the Table metadata proposal but
> were side-tracked on its use of TableIdentifier. There were good points
> about how Spark should identify tables, views, functions, etc, and I want
> to start a discussion here.
>
>
>
> Identifiers are orthogonal to the TableCatalog proposal that can be
> updated to use whatever identifier class we choose. That proposal is
> concerned with what information should be passed to define a table, and
> how to pass that information.
>
>
>
> The main question for this discussion is: *how should Spark identify
> tables, views, and functions when it supports multiple catalogs?*
>
>
>
> There are two main approaches:
>
> * Use a 3-part identifier, catalog.database.table
> * Use an identifier with an arbitrary number of parts
>
>
> *Option 1: use 3-part identifiers*
>
>
>
> The argument for option #1 is that it is simple. If an external data store
> has additional logical hierarchy layers, then that hierarchy would be
> mapped to multiple catalogs in Spark. Spark can support show tables and
> show databases without much trouble. This is the approach used by Presto,
> so there is some precedent for it.
>
>
>
> The drawback is that mapping a more complex hierarchy into Spark requires
> more configuration. If an external DB has a 3-level hierarchy — say, for
> example, schema.database.table — then option #1 requires users to configure
> a catalog for each top-level structure, each schema. When a new schema is
> added, it is not automatically accessible.
>
>
>
> Catalog implementations could use session options could provide a rough
> work-around by changing the plugin’s “current” schema. I think this is an
> anti-pattern, so another strike against this option is that it encourages
> bad practices.
>
>
>
> *Option 2: use n-part identifiers*
>
>
>
> That drawback for option #1 is the main argument for option #2: Spark
> should allow users to easily interact with the native structure of an
> external store. For option #2, a full identifier would be an
> arbitrary-length list of identifiers. For the example above, using catalog.schema.database.table
> is allowed. An identifier would be something like this:
>
> case class CatalogIdentifier(parts: Seq[String])
>
> The problem with option #2 is how to implement a listing and discovery
> API, for operations like SHOW TABLES. If the catalog API requires a list(ident:
> CatalogIdentifier) , what does it return? There is no guarantee that the
> listed objects are tables and not nested namespaces. How would Spark
> handle arbitrary nesting that differs across catalogs?
>
>
>
> Hopefully, I’ve captured the design question well enough for a productive
> discussion. Thanks!
>
>
>
> rb
>
>
> --
> Ryan Blue
> Software Engineer
> Netflix
>