Environment:
impalad version 2.8.0-cdh5.11.1 RELEASE (build
3382c1c488dff12d5ca8d049d2b59babee605b4e)
Built on Thu Jun 1 10:36:12 PDT 2017
kudu 1.1.0
revision 3f64b68724ded320a31b69467c5a5f7744b07a50
build type RELEASE
built by jenkins at 17 Nov 2016 00:34:09 PST on
impala-ec2-pkg-centos-7-0fe9.vpc.cloudera.com
build id 2016-11-17_00-00-22
I have a Kudu table that I’m trying to update through JDBC which fails silently
when trying to update a nullable column to null. When running the update
through the impala-shell it gives me a warning about violating a nullability
constraint. The table description is below. The column in question is the
sample_catalog_tags column at the bottom.
Query: describe samples_v3_1_dj
+--------------------------------------------+--------+---------+-------------+----------+---------------+---------------+---------------------+------------+
| name | type | comment | primary_key |
nullable | default_value | encoding | compression | block_size |
+--------------------------------------------+--------+---------+-------------+----------+---------------+---------------+---------------------+------------+
| sequence_result_id | string | | true |
false | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| sample_id | string | | false |
false | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| sample_name | string | | false |
false | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| species | string | | false |
false | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| locus | string | | false |
false | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| product_subtype | string | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| counting_method | string | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| primer_set | string | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| productive_entropy | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| productive_clonality | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| sample_entropy | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| sample_clonality | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| sample_amount_ng | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| sample_cells_mass_estimate | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| fraction_productive_of_cells_mass_estimate | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| sample_cells | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| fraction_productive_of_cells | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| max_productive_frequency | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| max_frequency | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| total_templates | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| productive_templates | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| outofframe_templates | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| stop_templates | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| dj_templates | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| total_rearrangements | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| productive_rearrangements | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| outofframe_rearrangements | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| stop_rearrangements | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| dj_rearrangements | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| total_reads | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| total_productive_reads | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| total_outofframe_reads | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| total_stop_reads | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| total_dj_reads | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| sequence_result_status | string | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| release_date | bigint | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| upload_date | bigint | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| fraction_productive | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| order_name | string | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| kit_id | string | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| kit_pool | string | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| owning_workspace_id | string | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| min_rearrangement_length | int | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| est_productive_receptors | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| est_total_receptors | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| sample_properties | string | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| total_templates_agg | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| total_t_cells | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| total_t_cells_new | double | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
| sample_catalog_tags | string | | false |
true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 |
+--------------------------------------------+--------+---------+-------------+----------+---------------+---------------+---------------------+------------+
Fetched 50 row(s) in 0.02s
When I run the update statement in the impala-shell I see the warning about
nullability and a subsequent select shows the column was not updated.
> select sample_id, sequence_result_id, sample_catalog_tags from
> samples_v3_1_dj WHERE sample_id IN ('1e6a74f5-8a2e-4806-b7bf-4cf1dd947899');
Query: select sample_id, sequence_result_id, sample_catalog_tags from
samples_v3_1_dj WHERE sample_id IN ('1e6a74f5-8a2e-4806-b7bf-4cf1dd947899')
Query submitted at: 2018-04-06 00:12:21 (Coordinator: http://...:25000)
Query progress can be monitored at:
http://...:25000/query_plan?query_id=d344cbbba783c2ce:b3be430e00000000
+--------------------------------------+--------------------------------------+-----------------------------------------+
| sample_id | sequence_result_id |
sample_catalog_tags |
+--------------------------------------+--------------------------------------+-----------------------------------------+
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | 280df143-b893-4e2b-b1e7-dfb9940aaf10 |
Tissue Source:Blood,Diagnosis:Diagnosis |
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | d10517c0-0aeb-4d73-96a6-db8ab9ca60d7 |
Tissue Source:Blood,Diagnosis:Diagnosis |
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | 19154086-868e-484f-9c7d-4d62434ca571 |
Tissue Source:Blood,Diagnosis:Diagnosis |
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | fbdc1f5e-5aa8-40e2-bc64-b97af03b4c66 |
Tissue Source:Blood,Diagnosis:Diagnosis |
+--------------------------------------+--------------------------------------+-----------------------------------------+
Fetched 4 row(s) in 0.23s
> UPDATE samples_v3_1_dj SET sample_catalog_tags=null WHERE sequence_result_id
> IN ('280df143-b893-4e2b-b1e7-dfb9940aaf10');
Query: update samples_v3_1_dj SET sample_catalog_tags=null WHERE
sequence_result_id IN ('280df143-b893-4e2b-b1e7-dfb9940aaf10')
Query submitted at: 2018-04-06 00:13:13 (Coordinator: http://...:25000)
Query progress can be monitored at:
http://...:25000/query_plan?query_id=f8427448b43445db:aa28650800000000
WARNINGS: Row with null value violates nullability constraint on table
'impala::immunoseq_analyzer.samples_v3_1_dj'.
Modified 0 row(s), 1 row error(s) in 0.34s
> select sample_id, sequence_result_id, sample_catalog_tags from
> samples_v3_1_dj WHERE sample_id IN ('1e6a74f5-8a2e-4806-b7bf-4cf1dd947899');
Query: select sample_id, sequence_result_id, sample_catalog_tags from
samples_v3_1_dj WHERE sample_id IN ('1e6a74f5-8a2e-4806-b7bf-4cf1dd947899')
Query submitted at: 2018-04-06 00:13:18 (Coordinator: http://...:25000)
Query progress can be monitored at:
http://...:25000/query_plan?query_id=e44ab2131f30dc65:eef1e66400000000
+--------------------------------------+--------------------------------------+-----------------------------------------+
| sample_id | sequence_result_id |
sample_catalog_tags |
+--------------------------------------+--------------------------------------+-----------------------------------------+
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | 280df143-b893-4e2b-b1e7-dfb9940aaf10 |
Tissue Source:Blood,Diagnosis:Diagnosis |
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | fbdc1f5e-5aa8-40e2-bc64-b97af03b4c66 |
Tissue Source:Blood,Diagnosis:Diagnosis |
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | d10517c0-0aeb-4d73-96a6-db8ab9ca60d7 |
Tissue Source:Blood,Diagnosis:Diagnosis |
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | 19154086-868e-484f-9c7d-4d62434ca571 |
Tissue Source:Blood,Diagnosis:Diagnosis |
+--------------------------------------+--------------------------------------+-----------------------------------------+
Fetched 4 row(s) in 0.24s
As you can see above the column is nullable but attempting to set the column to
null does not work. Below shows that there are other rows with null values for
this column.
> select sample_id, sequence_result_id, sample_catalog_tags from
> samples_v3_1_dj where sample_catalog_tags is null limit 1;
Query: select sample_id, sequence_result_id, sample_catalog_tags from
samples_v3_1_dj where sample_catalog_tags is null limit 1
Query submitted at: 2018-04-06 00:20:01 (Coordinator: http://...:25000)
Query progress can be monitored at:
http://...:25000/query_plan?query_id=604afe3b4fcb9c0c:364161d100000000
+--------------------------------------+--------------------------------------+---------------------+
| sample_id | sequence_result_id |
sample_catalog_tags |
+--------------------------------------+--------------------------------------+---------------------+
| b76eaccd-da3f-49de-b04a-28ad3cb112d5 | 0008df4c-01f0-4e7f-9493-954a1e0530bd |
NULL |
+--------------------------------------+--------------------------------------+---------------------+
Fetched 1 row(s) in 0.33s
Now if I attempt an upsert you can see that it does in fact work.
> upsert into table samples_v3_1_dj (sequence_result_id, sample_id,
> sample_name, species, locus, product_subtype, sample_catalog_tags) values
> ('280df143-b893-4e2b-b1e7-dfb9940aaf10',
> '1e6a74f5-8a2e-4806-b7bf-4cf1dd947899', 'SAMPLE-4', 'Human', 'TCRB',
> 'Survey', null);
Query: upsert into table samples_v3_1_dj (sequence_result_id, sample_id,
sample_name, species, locus, product_subtype, sample_catalog_tags) values
('280df143-b893-4e2b-b1e7-dfb9940aaf10',
'1e6a74f5-8a2e-4806-b7bf-4cf1dd947899', 'SAMPLE-4', 'Human', 'TCRB', 'Survey',
null)
Query submitted at: 2018-04-06 00:21:44 (Coordinator: http://...:25000)
Query progress can be monitored at:
http://...:25000/query_plan?query_id=b942ea9871581947:b363f6d000000000
Modified 1 row(s), 0 row error(s) in 0.11s
> select sample_id, sequence_result_id, sample_catalog_tags from
> samples_v3_1_dj WHERE sample_id IN ('1e6a74f5-8a2e-4806-b7bf-4cf1dd947899');
Query: select sample_id, sequence_result_id, sample_catalog_tags from
samples_v3_1_dj WHERE sample_id IN ('1e6a74f5-8a2e-4806-b7bf-4cf1dd947899')
Query submitted at: 2018-04-06 00:21:51 (Coordinator: http://...:25000)
Query progress can be monitored at:
http://...:25000/query_plan?query_id=f3485ee6580ae44f:b8c5242100000000
+--------------------------------------+--------------------------------------+-----------------------------------------+
| sample_id | sequence_result_id |
sample_catalog_tags |
+--------------------------------------+--------------------------------------+-----------------------------------------+
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | 280df143-b893-4e2b-b1e7-dfb9940aaf10 |
NULL |
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | d10517c0-0aeb-4d73-96a6-db8ab9ca60d7 |
Tissue Source:Blood,Diagnosis:Diagnosis |
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | 19154086-868e-484f-9c7d-4d62434ca571 |
Tissue Source:Blood,Diagnosis:Diagnosis |
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | fbdc1f5e-5aa8-40e2-bc64-b97af03b4c66 |
Tissue Source:Blood,Diagnosis:Diagnosis |
+--------------------------------------+--------------------------------------+-----------------------------------------+
Fetched 4 row(s) in 0.33s
I suppose I could change my update to an upsert…select…from but it’s still
bugging me that the update isn’t working.
> upsert into table samples_v3_1_dj (sequence_result_id, sample_id,
> sample_name, species, locus, product_subtype, sample_catalog_tags) select
> sequence_result_id, sample_id, sample_name, species, locus, product_subtype,
> null from samples_v3_1_dj where sample_id =
> '1e6a74f5-8a2e-4806-b7bf-4cf1dd947899';
Query: upsert into table samples_v3_1_dj (sequence_result_id, sample_id,
sample_name, species, locus, product_subtype, sample_catalog_tags) select
sequence_result_id, sample_id, sample_name, species, locus, product_subtype,
null from samples_v3_1_dj where sample_id =
'1e6a74f5-8a2e-4806-b7bf-4cf1dd947899'
Query submitted at: 2018-04-06 00:28:28 (Coordinator: http://...:25000)
Query progress can be monitored at:
http://...:25000/query_plan?query_id=8d422b3c7ff44356:69fa874300000000
Modified 4 row(s), 0 row error(s) in 0.44s
> select sample_id, sequence_result_id, sample_catalog_tags from
> samples_v3_1_dj WHERE sample_id IN ('1e6a74f5-8a2e-4806-b7bf-4cf1dd947899');
Query: select sample_id, sequence_result_id, sample_catalog_tags from
samples_v3_1_dj WHERE sample_id IN ('1e6a74f5-8a2e-4806-b7bf-4cf1dd947899')
Query submitted at: 2018-04-06 00:28:34 (Coordinator: http://...:25000)
Query progress can be monitored at:
http://...:25000/query_plan?query_id=447bcd28973cd84:b76c1b2b00000000
+--------------------------------------+--------------------------------------+---------------------+
| sample_id | sequence_result_id |
sample_catalog_tags |
+--------------------------------------+--------------------------------------+---------------------+
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | fbdc1f5e-5aa8-40e2-bc64-b97af03b4c66 |
NULL |
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | d10517c0-0aeb-4d73-96a6-db8ab9ca60d7 |
NULL |
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | 19154086-868e-484f-9c7d-4d62434ca571 |
NULL |
| 1e6a74f5-8a2e-4806-b7bf-4cf1dd947899 | 280df143-b893-4e2b-b1e7-dfb9940aaf10 |
NULL |
+--------------------------------------+--------------------------------------+---------------------+
Fetched 4 row(s) in 0.17s
Any thoughts on how I could get the update to work?
Thanks,
Jeff
This message (and any attachments) is intended only for the designated
recipient(s). It
may contain confidential or proprietary information, or have other limitations
on use as
indicated by the sender. If you are not a designated recipient, you may not
review, use,
copy or distribute this message. If you received this in error, please notify
the sender by
reply e-mail and delete this message.
