Yes. I was quoting the column name. It did allow me to alter table and drop the column with the quotes.However it threw an error when I tried to alter table and add the column with the right data type.In production we are upserting into a new table like you suggest. I was trying to find a more seamless approach.
Sent from my iPhone
On Feb 13, 2014, at 5:44 PM, James Taylor <firstname.lastname@example.org> wrote:Did you know you can put any table/column reference in double quotes in any Phoenix statement and it'll be case sensitive? For example: select * from "foo" where "myCol" = 5We don't support changing data types in an ALTER TABLE statement, but another pretty easy data migration option is the UPSERT SELECT command to copy data from one table to another.
On Thu, Feb 13, 2014 at 4:32 PM, Justin Workman <email@example.com> wrote:This is in testing. I hope we wouldn't need to in production.Use case is that I created a Phoenix table over an existing hbase table and some of the columns were defined with the wrong data type, we also did not define all of the columns (with the intent to use dynamic columns if we needed to). Because of the way the data was populated, camel case column names, we cannot alter the table definition through Phoenix, we get an exception expecting a normalize column name (likely a bug?). So we were hoping to just drop the Phoenix table and recreate it, but it appears it truncated the data.I do believe a view would be better suited here, especially if we could create an index on the view itself.
Sent from my iPhone
On Feb 13, 2014, at 5:19 PM, James Taylor <firstname.lastname@example.org> wrote:
Thanks,Just curious - can you tell me a bit more about your use case and why you need to drop the phoenix table?Or you can do a query/scan at an earlier timestamp to see your data (i.e. scan.setTimeRange(0,tsBeforeYouDeleted)). In Phoenix 3.0, we have extended the VIEW capabilities: http://phoenix.incubator.apache.org/views.htmlThis might be more what you're looking for, as dropping a view would not affect the data.
JamesOn Thu, Feb 13, 2014 at 4:08 PM, Justin Workman <email@example.com> wrote:
So is there not a way to drop the table in Phoenix and not drop/truncate the table and data in HBase? For this particular use case, this was a table already in HBase, and the data is static so we only have a single version of data in the table. So when we drop the table in Phoenix, the data is marked for deletion and we cannot see any data via HBase scan unless we do a RAW scan.On Thu, Feb 13, 2014 at 2:17 PM, James Taylor <firstname.lastname@example.org> wrote:
Yes, phoenix.schema.dropMetaData is a client-side setting.Thanks,
You can create a VIEW instead of a TABLE and your data won't be deleted. However, performance won't be as good as with a TABLE under some circumstances. Also, you wouldn't be able to use mutable secondary indexing with VIEWs. Note also, the old data is still there - we've just added Delete markers. You can actually connect at an "earlier" timestamp and continue to query the old data: http://phoenix.incubator.apache.org/faq.html#/Can_phoenix_work_on_tables_with_arbitrary_timestamp_as_flexible_as_HBase_API
JamesOn Thu, Feb 13, 2014 at 1:11 PM, Justin Workman <email@example.com> wrote:
I'm looking for some guidance on the proper way to drop a table from Phoenix yet preserve the underlying HBase table and data? I have read here, https://github.com/forcedotcom/phoenix/issues/219, that I should be able to set a value to false and have the table preserved in HBase. Is this a client side or server side setting?I added that value, phoenix.schema.dropMetaData, to my clients hbase-site.xml and set it to false and when I dropped the table in Phoenix, it did preserve the table in HBase, however it truncated all of the data.Is there anyway to preserve both the table and data when dropping a table in Phoenix?