You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@sentry.apache.org by Brock Noland <br...@cloudera.com> on 2014/02/14 22:30:26 UTC

Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/
-----------------------------------------------------------

Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.


Bugs: SENTRY-117
    https://issues.apache.org/jira/browse/SENTRY-117


Repository: sentry


Description
-------

Database schemas for the three production supported databases.


Diffs
-----

  sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
  sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
  sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 

Diff: https://reviews.apache.org/r/18145/diff/


Testing
-------

I was able to execute all statements in the schemas on the various databases types.


Thanks,

Brock Noland


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Brock Noland <br...@cloudera.com>.

> On Feb. 16, 2014, 3:16 p.m., Jarek Cecho wrote:
> > sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql, lines 14-20
> > <https://reviews.apache.org/r/18145/diff/2/?file=486067#file486067line14>
> >
> >     Considering that we are going to deal with data in UTF, wouldn't be better to use the same character set on database as well?

I copied this from Hive. I looked into it and it seems like setting UTF8 on mysql had to be done at the server level? I will look into this more.


> On Feb. 16, 2014, 3:16 p.m., Jarek Cecho wrote:
> > sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql, line 21
> > <https://reviews.apache.org/r/18145/diff/2/?file=486067#file486067line21>
> >
> >     Is there particular reason why time value is stored as bigint and not as a datetime/timestamp?

Good question, I copied that. Let me look into DN data time support across these dbs.


- Brock


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34596
-----------------------------------------------------------


On Feb. 14, 2014, 9:32 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 14, 2014, 9:32 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Jarek Cecho <ja...@apache.org>.

> On Feb. 16, 2014, 3:16 p.m., Jarek Cecho wrote:
> > sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql, line 21
> > <https://reviews.apache.org/r/18145/diff/2/?file=486067#file486067line21>
> >
> >     Is there particular reason why time value is stored as bigint and not as a datetime/timestamp?
> 
> Brock Noland wrote:
>     Good question, I copied that. Let me look into DN data time support across these dbs.
> 
> Brock Noland wrote:
>     Shreepadma, what are you thoughts on this one?
> 
> Shreepadma Venugopalan wrote:
>     Thrift doesn't support timestamp/datetime type, so the value is serialized as long over the wire. If we can consistently support timestamp/datetime across dbs (derby, mysql, postgres, oracle) we can convert from long to timestamp before persisting the value.
> 
> Brock Noland wrote:
>     I know that it's not technically ideal, but I don't see a huge disadvantage to storing a timestamp as a bigint?

Me neither. I'm fine with (big)int if it's to make our life easier on the Thrift side.


- Jarek


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34596
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Shreepadma Venugopalan <sh...@cloudera.com>.

> On Feb. 16, 2014, 3:16 p.m., Jarek Cecho wrote:
> > sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql, line 21
> > <https://reviews.apache.org/r/18145/diff/2/?file=486067#file486067line21>
> >
> >     Is there particular reason why time value is stored as bigint and not as a datetime/timestamp?
> 
> Brock Noland wrote:
>     Good question, I copied that. Let me look into DN data time support across these dbs.
> 
> Brock Noland wrote:
>     Shreepadma, what are you thoughts on this one?

Thrift doesn't support timestamp/datetime type, so the value is serialized as long over the wire. If we can consistently support timestamp/datetime across dbs (derby, mysql, postgres, oracle) we can convert from long to timestamp before persisting the value.


- Shreepadma


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34596
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Brock Noland <br...@cloudera.com>.

> On Feb. 16, 2014, 3:16 p.m., Jarek Cecho wrote:
> > sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql, line 21
> > <https://reviews.apache.org/r/18145/diff/2/?file=486067#file486067line21>
> >
> >     Is there particular reason why time value is stored as bigint and not as a datetime/timestamp?
> 
> Brock Noland wrote:
>     Good question, I copied that. Let me look into DN data time support across these dbs.
> 
> Brock Noland wrote:
>     Shreepadma, what are you thoughts on this one?
> 
> Shreepadma Venugopalan wrote:
>     Thrift doesn't support timestamp/datetime type, so the value is serialized as long over the wire. If we can consistently support timestamp/datetime across dbs (derby, mysql, postgres, oracle) we can convert from long to timestamp before persisting the value.

I know that it's not technically ideal, but I don't see a huge disadvantage to storing a timestamp as a bigint?


- Brock


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34596
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Brock Noland <br...@cloudera.com>.

> On Feb. 16, 2014, 3:16 p.m., Jarek Cecho wrote:
> > sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql, line 21
> > <https://reviews.apache.org/r/18145/diff/2/?file=486067#file486067line21>
> >
> >     Is there particular reason why time value is stored as bigint and not as a datetime/timestamp?
> 
> Brock Noland wrote:
>     Good question, I copied that. Let me look into DN data time support across these dbs.

Shreepadma, what are you thoughts on this one?


- Brock


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34596
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Jarek Cecho <ja...@apache.org>.

> On Feb. 16, 2014, 3:16 p.m., Jarek Cecho wrote:
> > sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql, lines 14-20
> > <https://reviews.apache.org/r/18145/diff/2/?file=486067#file486067line14>
> >
> >     Considering that we are going to deal with data in UTF, wouldn't be better to use the same character set on database as well?
> 
> Brock Noland wrote:
>     I copied this from Hive. I looked into it and it seems like setting UTF8 on mysql had to be done at the server level? I will look into this more.

Not at all, utf8 is encoding as any other and hence can be used there as well.


- Jarek


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34596
-----------------------------------------------------------


On Feb. 14, 2014, 9:32 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 14, 2014, 9:32 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Jarek Cecho <ja...@apache.org>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34596
-----------------------------------------------------------


Thank you for the patch Brock! Couple of notes:


sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql
<https://reviews.apache.org/r/18145/#comment64754>

    Considering that we are going to deal with data in UTF, wouldn't be better to use the same character set on database as well?



sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql
<https://reviews.apache.org/r/18145/#comment64755>

    Is there particular reason why time value is stored as bigint and not as a datetime/timestamp?


Jarcec

- Jarek Cecho


On Feb. 14, 2014, 9:32 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 14, 2014, 9:32 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Shreepadma Venugopalan <sh...@cloudera.com>.

> On Feb. 20, 2014, 1:34 a.m., Shreepadma Venugopalan wrote:
> > Is the purpose of the version to detect if the schema creation has succeeded?
> 
> Brock Noland wrote:
>     Just to do versioning like we do with the metastore. Eventually, we obviously don't need it until version 2.
> 
> Shreepadma Venugopalan wrote:
>     Yeah. The only problem I see is SQL statements will not execute atomically on all databases, so we can't really use it to tell if the schema creation/upgrade succeeded. This is true in both Oracle and Postgres, though in Postgres enclosing it in begin transaction/commit block would work. However, even that will not work in Oracle.
> 
> Brock Noland wrote:
>     But they should execute in order, right? Meaning we can detect it occurred this time if the row exists in the table and for future updates we do the update as the last statement.
>     
>     In any case, this is the same thing Prasad did for Hive.

I don't follow - are we suggesting that we'll validate the tables after running the script?


- Shreepadma


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34959
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Brock Noland <br...@cloudera.com>.

> On Feb. 20, 2014, 1:34 a.m., Shreepadma Venugopalan wrote:
> > Is the purpose of the version to detect if the schema creation has succeeded?

Just to do versioning like we do with the metastore. Eventually, we obviously don't need it until version 2.


- Brock


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34959
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Brock Noland <br...@cloudera.com>.

> On Feb. 20, 2014, 1:34 a.m., Shreepadma Venugopalan wrote:
> > Is the purpose of the version to detect if the schema creation has succeeded?
> 
> Brock Noland wrote:
>     Just to do versioning like we do with the metastore. Eventually, we obviously don't need it until version 2.
> 
> Shreepadma Venugopalan wrote:
>     Yeah. The only problem I see is SQL statements will not execute atomically on all databases, so we can't really use it to tell if the schema creation/upgrade succeeded. This is true in both Oracle and Postgres, though in Postgres enclosing it in begin transaction/commit block would work. However, even that will not work in Oracle.
> 
> Brock Noland wrote:
>     But they should execute in order, right? Meaning we can detect it occurred this time if the row exists in the table and for future updates we do the update as the last statement.
>     
>     In any case, this is the same thing Prasad did for Hive.
> 
> Shreepadma Venugopalan wrote:
>     I don't follow - are we suggesting that we'll validate the tables after running the script?
> 
> Brock Noland wrote:
>     Not today, but in the future I think we'll have a tool similar to the HiveSchemaTool which does the validation.
> 
> Shreepadma Venugopalan wrote:
>     HiveSchemaTool runs the upgrade/creation script one statement at a time and if any of the statement fails, it aborts. Thus the version is not updated unless all the statements are executed successfully. In the absence of such a tool, do you agree updating the version doesn't mean much?

Right, as I said this will not be used today....it's there for the next release. If you have concerns that we shouldn't add it until we add SentrySchemaTool, we can remove it.


- Brock


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34959
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Shreepadma Venugopalan <sh...@cloudera.com>.

> On Feb. 20, 2014, 1:34 a.m., Shreepadma Venugopalan wrote:
> > Is the purpose of the version to detect if the schema creation has succeeded?
> 
> Brock Noland wrote:
>     Just to do versioning like we do with the metastore. Eventually, we obviously don't need it until version 2.
> 
> Shreepadma Venugopalan wrote:
>     Yeah. The only problem I see is SQL statements will not execute atomically on all databases, so we can't really use it to tell if the schema creation/upgrade succeeded. This is true in both Oracle and Postgres, though in Postgres enclosing it in begin transaction/commit block would work. However, even that will not work in Oracle.
> 
> Brock Noland wrote:
>     But they should execute in order, right? Meaning we can detect it occurred this time if the row exists in the table and for future updates we do the update as the last statement.
>     
>     In any case, this is the same thing Prasad did for Hive.
> 
> Shreepadma Venugopalan wrote:
>     I don't follow - are we suggesting that we'll validate the tables after running the script?
> 
> Brock Noland wrote:
>     Not today, but in the future I think we'll have a tool similar to the HiveSchemaTool which does the validation.

HiveSchemaTool runs the upgrade/creation script one statement at a time and if any of the statement fails, it aborts. Thus the version is not updated unless all the statements are executed successfully. In the absence of such a tool, do you agree updating the version doesn't mean much?


- Shreepadma


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34959
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Brock Noland <br...@cloudera.com>.

> On Feb. 20, 2014, 1:34 a.m., Shreepadma Venugopalan wrote:
> > Is the purpose of the version to detect if the schema creation has succeeded?
> 
> Brock Noland wrote:
>     Just to do versioning like we do with the metastore. Eventually, we obviously don't need it until version 2.
> 
> Shreepadma Venugopalan wrote:
>     Yeah. The only problem I see is SQL statements will not execute atomically on all databases, so we can't really use it to tell if the schema creation/upgrade succeeded. This is true in both Oracle and Postgres, though in Postgres enclosing it in begin transaction/commit block would work. However, even that will not work in Oracle.

But they should execute in order, right? Meaning we can detect it occurred this time if the row exists in the table and for future updates we do the update as the last statement.

In any case, this is the same thing Prasad did for Hive.


- Brock


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34959
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Shreepadma Venugopalan <sh...@cloudera.com>.

> On Feb. 20, 2014, 1:34 a.m., Shreepadma Venugopalan wrote:
> > Is the purpose of the version to detect if the schema creation has succeeded?
> 
> Brock Noland wrote:
>     Just to do versioning like we do with the metastore. Eventually, we obviously don't need it until version 2.
> 
> Shreepadma Venugopalan wrote:
>     Yeah. The only problem I see is SQL statements will not execute atomically on all databases, so we can't really use it to tell if the schema creation/upgrade succeeded. This is true in both Oracle and Postgres, though in Postgres enclosing it in begin transaction/commit block would work. However, even that will not work in Oracle.
> 
> Brock Noland wrote:
>     But they should execute in order, right? Meaning we can detect it occurred this time if the row exists in the table and for future updates we do the update as the last statement.
>     
>     In any case, this is the same thing Prasad did for Hive.
> 
> Shreepadma Venugopalan wrote:
>     I don't follow - are we suggesting that we'll validate the tables after running the script?
> 
> Brock Noland wrote:
>     Not today, but in the future I think we'll have a tool similar to the HiveSchemaTool which does the validation.
> 
> Shreepadma Venugopalan wrote:
>     HiveSchemaTool runs the upgrade/creation script one statement at a time and if any of the statement fails, it aborts. Thus the version is not updated unless all the statements are executed successfully. In the absence of such a tool, do you agree updating the version doesn't mean much?
> 
> Brock Noland wrote:
>     Right, as I said this will not be used today....it's there for the next release. If you have concerns that we shouldn't add it until we add SentrySchemaTool, we can remove it.

Nah, let's keep it. That way we don't have to worry about upgrade. But we need to clearly call out that the version can't be used for schema integrity yet.


- Shreepadma


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34959
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Brock Noland <br...@cloudera.com>.

> On Feb. 20, 2014, 1:34 a.m., Shreepadma Venugopalan wrote:
> > Is the purpose of the version to detect if the schema creation has succeeded?
> 
> Brock Noland wrote:
>     Just to do versioning like we do with the metastore. Eventually, we obviously don't need it until version 2.
> 
> Shreepadma Venugopalan wrote:
>     Yeah. The only problem I see is SQL statements will not execute atomically on all databases, so we can't really use it to tell if the schema creation/upgrade succeeded. This is true in both Oracle and Postgres, though in Postgres enclosing it in begin transaction/commit block would work. However, even that will not work in Oracle.
> 
> Brock Noland wrote:
>     But they should execute in order, right? Meaning we can detect it occurred this time if the row exists in the table and for future updates we do the update as the last statement.
>     
>     In any case, this is the same thing Prasad did for Hive.
> 
> Shreepadma Venugopalan wrote:
>     I don't follow - are we suggesting that we'll validate the tables after running the script?

Not today, but in the future I think we'll have a tool similar to the HiveSchemaTool which does the validation.


- Brock


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34959
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Shreepadma Venugopalan <sh...@cloudera.com>.

> On Feb. 20, 2014, 1:34 a.m., Shreepadma Venugopalan wrote:
> > Is the purpose of the version to detect if the schema creation has succeeded?
> 
> Brock Noland wrote:
>     Just to do versioning like we do with the metastore. Eventually, we obviously don't need it until version 2.

Yeah. The only problem I see is SQL statements will not execute atomically on all databases, so we can't really use it to tell if the schema creation/upgrade succeeded. This is true in both Oracle and Postgres, though in Postgres enclosing it in begin transaction/commit block would work. However, even that will not work in Oracle.


- Shreepadma


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34959
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Shreepadma Venugopalan <sh...@cloudera.com>.

> On Feb. 20, 2014, 1:34 a.m., Shreepadma Venugopalan wrote:
> > Is the purpose of the version to detect if the schema creation has succeeded?
> 
> Brock Noland wrote:
>     Just to do versioning like we do with the metastore. Eventually, we obviously don't need it until version 2.
> 
> Shreepadma Venugopalan wrote:
>     Yeah. The only problem I see is SQL statements will not execute atomically on all databases, so we can't really use it to tell if the schema creation/upgrade succeeded. This is true in both Oracle and Postgres, though in Postgres enclosing it in begin transaction/commit block would work. However, even that will not work in Oracle.
> 
> Brock Noland wrote:
>     But they should execute in order, right? Meaning we can detect it occurred this time if the row exists in the table and for future updates we do the update as the last statement.
>     
>     In any case, this is the same thing Prasad did for Hive.
> 
> Shreepadma Venugopalan wrote:
>     I don't follow - are we suggesting that we'll validate the tables after running the script?
> 
> Brock Noland wrote:
>     Not today, but in the future I think we'll have a tool similar to the HiveSchemaTool which does the validation.
> 
> Shreepadma Venugopalan wrote:
>     HiveSchemaTool runs the upgrade/creation script one statement at a time and if any of the statement fails, it aborts. Thus the version is not updated unless all the statements are executed successfully. In the absence of such a tool, do you agree updating the version doesn't mean much?
> 
> Brock Noland wrote:
>     Right, as I said this will not be used today....it's there for the next release. If you have concerns that we shouldn't add it until we add SentrySchemaTool, we can remove it.
> 
> Shreepadma Venugopalan wrote:
>     Nah, let's keep it. That way we don't have to worry about upgrade. But we need to clearly call out that the version can't be used for schema integrity yet.

Can you enclose the postgres script with a being transaction/commit block? Thanks!


- Shreepadma


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34959
-----------------------------------------------------------


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Shreepadma Venugopalan <sh...@cloudera.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review34959
-----------------------------------------------------------


Is the purpose of the version to detect if the schema creation has succeeded?

- Shreepadma Venugopalan


On Feb. 17, 2014, 5:45 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 17, 2014, 5:45 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Shreepadma Venugopalan <sh...@cloudera.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/#review35097
-----------------------------------------------------------

Ship it!


Ship It!

- Shreepadma Venugopalan


On Feb. 20, 2014, 10:35 p.m., Brock Noland wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/18145/
> -----------------------------------------------------------
> 
> (Updated Feb. 20, 2014, 10:35 p.m.)
> 
> 
> Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.
> 
> 
> Bugs: SENTRY-117
>     https://issues.apache.org/jira/browse/SENTRY-117
> 
> 
> Repository: sentry
> 
> 
> Description
> -------
> 
> Database schemas for the three production supported databases.
> 
> 
> Diffs
> -----
> 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
>   sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/18145/diff/
> 
> 
> Testing
> -------
> 
> I was able to execute all statements in the schemas on the various databases types.
> 
> 
> Thanks,
> 
> Brock Noland
> 
>


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Brock Noland <br...@cloudera.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/
-----------------------------------------------------------

(Updated Feb. 20, 2014, 10:35 p.m.)


Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.


Changes
-------

Added being/commit stuff;


Bugs: SENTRY-117
    https://issues.apache.org/jira/browse/SENTRY-117


Repository: sentry


Description
-------

Database schemas for the three production supported databases.


Diffs (updated)
-----

  sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
  sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
  sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 

Diff: https://reviews.apache.org/r/18145/diff/


Testing
-------

I was able to execute all statements in the schemas on the various databases types.


Thanks,

Brock Noland


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Brock Noland <br...@cloudera.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/
-----------------------------------------------------------

(Updated Feb. 17, 2014, 5:45 p.m.)


Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.


Changes
-------

Patch addresses the character encoding issue.


Bugs: SENTRY-117
    https://issues.apache.org/jira/browse/SENTRY-117


Repository: sentry


Description
-------

Database schemas for the three production supported databases.


Diffs (updated)
-----

  sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
  sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
  sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 

Diff: https://reviews.apache.org/r/18145/diff/


Testing
-------

I was able to execute all statements in the schemas on the various databases types.


Thanks,

Brock Noland


Re: Review Request 18145: SENTRY-117 Create database schemas for mysql, postgres, and oracle

Posted by Brock Noland <br...@cloudera.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/18145/
-----------------------------------------------------------

(Updated Feb. 14, 2014, 9:32 p.m.)


Review request for sentry, Jarek Cecho and Shreepadma Venugopalan.


Changes
-------

Remove one trailing space


Bugs: SENTRY-117
    https://issues.apache.org/jira/browse/SENTRY-117


Repository: sentry


Description
-------

Database schemas for the three production supported databases.


Diffs (updated)
-----

  sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql PRE-CREATION 
  sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql PRE-CREATION 
  sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql PRE-CREATION 

Diff: https://reviews.apache.org/r/18145/diff/


Testing
-------

I was able to execute all statements in the schemas on the various databases types.


Thanks,

Brock Noland