Yes, that configuration should work. But you should we aware that you now have 2 instances that can modify the hive metadata without locking at the hive level. Meaning, the database is the one that is in-charge of concurrency. Say you have a HMS client on one reading the full HMS snapshot via one instance, and at the same time, another client is modifying the metadata via the other instance, you may (or may not) see exceptions in the log about certain cached objects (in datanucleus) were no longer found. The HMS handler has retrying logic in it that will retry the transaction and succeed. It is safe to ignore the exceptions as long as the queries are succeeding. Hope this helps. Thanks ... View more

The JsonSerDe class is in hive-hcatalog-xxx.jar.  So you will have to run "ADD JAR" on this file prior.   JsonSerDe has been made a first class citizen recently via https://jira.apache.org/jira/browse/HIVE-18785. With this fix you should be able to just use "STORED AS JSONFILE" instead of having to specify the ROW FORMAT SERDE   This fix is available in CDH6.1 ( I believe, dont quote me on this)   Hope this helps. Thanks ... View more

There is not information for us to provide any guidance on what could be wrong. Do you believe that the hive results are inaccurate or the impala results? Could you provide the following items for us to look at? 1) full table definition in hive 2) Full or Sample data to help reproduce the issue. 3) Queries results from both Hive and impala for the sample data above. Thanks ... View more

the principal is the username that will be passed thru to HS2 for authentication (in your case, user to be authenticated against LDAP). This will be the same user that the jobs will run as in yarn/MR (if impersonation is turned on in Hive). It is not the kerberos-related. It is equivalent to -n command option for beeline. Hope this helps.  ... View more

Its not quite clear what the issue is. We will probably need HS2 and spark logs to do understand the issue.   However, I am curious if the second step succeeded. "load data inpath '/tmp/new.txt' into table new_tmp;"   This appears to be a local path but there is no "LOCAL" keyword in the command. Have you verfied that the data was actually inserted into new_tmp table after this step?   Also what version of CDH is this? Thanks ... View more

Hello, I am a Hive engineer so I am not quite certain of the HUE side settings but like you said the first step should be to narrow down if it is a hive-side or a hue-side issue. Testing from Beeline makes sense to ensure that hive-side settings are good.   To beeline JDBC URL, you can add the TrustStore localtion and credentials for it to use SSL connection. Something like this   beeline -u "jdbc:hive2://<HS2HostFQN>:10000/default;ssl=true;sslTrustStore=/etc/cdep-ssl-conf/signer/truststore.jks;sslTrustPassword=<pwd>;principal=<user>"   Let me know what you find. Thanks ... View more

Currently, for PARQUET files, I do not believe we support the property 'serialization.null.format'. So it will be ignored. Do you happen to have the exception from the log? how long did it run before it failed? ... View more

The bold text is used to tell hive how to read/interpret the data for the hive table (located at '/user/hive/warehouse/original_access_logs' in this case). 1) ROW FORMAT SERDE "org.apache.hadoop.hive.contrib.serde2.RegexSerDe" -- tells hive to use this class to serialize and deserialize the rows to/from the file. 2) input.regex is a property used by this class (RegexSerde) to deserialize the rows read from the table data. So this regex pattern is applied to the row value read from the file to split up into different columns defined in the meta data for this hive table. 3) output.format.string is a property used by this class (RegexSerde) to serialize the rows being written out to this table data. This value is used as a format to generate a row value (from its column values) that is to be written back to the output file for this hive table. Hope this helps. Thanks ... View more

I think I am lacking a bit of a context to your question because I am not sure where that text in bold is from. But generally speaking, the data within the partition is meant to be indicative of value for the partition column. For example, if your table has a partition column of "location", then all data under "Location=US" should belong to that geographical location. Otherwise, the queries involving this partition, generates incorrect result set. Hope this helps and let me know if you have additional questions. Thanks ... View more

Thanks for the info. Those props look right to me. Also quickly, ensure that the schema version is correct. ( I have no reason to believe it is wrong but just for check it off). Using Oracle DB client, login to oracle DB, and run select * from VERSION; the SCHEMA_VERSION should be 1.1.0. This error is thrown by the datanucleus layer. Its hard to know the exact cause as to what triggers this from the JDO layer. The following jira, with the same error message, suggests some kinda thread contention in the PMF implementation. http://www.datanucleus.org/servlet/jira/browse/NUCRDBMS-587 Perhaps this can help with some clues. ... View more

Is there additional stack from this exception? if so could you please post it? Also what version of CDH is this? Can you also post the value of the following properties in hive-site.xml? javax.jdo.option.ConnectionURL javax.jdo.option.ConnectionDriverName javax.jdo.option.ConnectionUserName javax.jdo.option.ConnectionPassword Thanks ... View more

Hey Suresh, Thanks for the clarification. I am not sure if that is supported today. Is there an example of another hive command that works today? Thanks ... View more

I am not quite sure what the end goal is here but for hive tables that have vast amounts of data and if processing them is a concern, you could use partitions on the hive table. You could find some meaning way to divide the data into smaller files (example: by data or some range of values for columns etc). Hope this helps. ... View more

Hey Suresh, Did you get a chance to try this? Let me know how this goes. Thanks ... View more

The path used in the add jar command is actually a local path. This command was added back in the Hive CLI days. With Beeline/HiveServer2 being used prominently. a better ways of doing this would be to use the hive.aux.jars.path configuration. Below is the documentation link on how to do this. https://www.cloudera.com/documentation/enterprise/5-6-x/topics/cm_mc_hive_udf.html#concept_cst_vms_lr Please let me know if you have additional questions. Thanks ... View more

Hey, First you want to ensure that variable substitution is not disabled in your hive environment. So check the value of "hive.variable.substitute" property in your configuration. A few examples are provided in the documentation wiki below. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+VariableSubstitution There are multiple namespaces in hive (system, hiveconf, env etc). env looks at the environment variables. I think you want to use something other than hiveconf (because it meant for propertiesi n hive-site.xml file) Please find examples below and let me know if you still need help. Thanks ... View more

Hey Suresh, What version of CDH are you on? This should work. Here is a simple test (from one of the test files) create table ptestfilter (a string, b int) partitioned by (c string, d string); describe ptestfilter; alter table ptestfilter add partition (c='US', d=1); alter table ptestfilter add partition (c='US', d=2); alter table ptestFilter add partition (c='Uganda', d=2); alter table ptestfilter add partition (c='Germany', d=2); alter table ptestfilter add partition (c='Canada', d=3); alter table ptestfilter add partition (c='Russia', d=3); alter table ptestfilter add partition (c='Greece', d=2); alter table ptestfilter add partition (c='India', d=3); alter table ptestfilter add partition (c='France', d=4); show partitions ptestfilter; // this should drop all partitions except where c='US' alter table ptestfilter drop partition (c<>'US', d>'0'); // alternatively you could also use NOT EQUALS operator to accomplish the same thing alter table ptestfilter drop partition (c != 'US', d>'0'); Let me know if this answers your question. Thanks ... View more

For comparision, can you post what it took on other releases that you may have tried? Also, would you be able to post the full query, if its ok to publish it to a public forum. ... View more

Hey Alina, I have tried this with CDH5.3.10 (a bit newer than your version) and with the new CDH releases, and round seems to be functioning as expected.   Connected to: Apache Hive (version 0.13.1-cdh5.3.10) Driver: Hive JDBC (version 0.13.1-cdh5.3.10) Transaction isolation: TRANSACTION_REPEATABLE_READ Beeline version 0.13.1-cdh5.3.10 by Apache Hive 0: jdbc:hive2://localhost:10000/default> show databases; +--------------------------------------------------------------------------------------------------+--+ | database_name | +--------------------------------------------------------------------------------------------------+--+ | cloudera_manager_metastore_canary_test_db_hive_1_hivemetastore_03567ed21d1b892110ff1cd925ae25bd | | default | +--------------------------------------------------------------------------------------------------+--+ 2 rows selected (1.916 seconds) 0: jdbc:hive2://localhost:10000/default> use default; No rows affected (0.127 seconds) 0: jdbc:hive2://localhost:10000/default> show tables; +------------+--+ | tab_name | +------------+--+ | sample_07 | | sample_08 | +------------+--+ 2 rows selected (0.153 seconds) 0: jdbc:hive2://localhost:10000/default> select round((20456079/100000),5); +------------+--+ | _c0 | +------------+--+ | 204.56079 | +------------+--+ 1 row selected (30.771 seconds) 0: jdbc:hive2://localhost:10000/default> select round((20456079/100000),1); +--------+--+ | _c0 | +--------+--+ | 204.6 | +--------+--+ 1 row selected (23.981 seconds) 0: jdbc:hive2://localhost:10000/default> select round((20456079/100000),3); +----------+--+ | _c0 | +----------+--+ | 204.561 | +----------+--+ 1 row selected (23.257 seconds) 0: jdbc:hive2://localhost:10000/default> select round(150,3); +------+--+ | _c0 | +------+--+ | 150 | +------+--+ 1 row selected (22.973 seconds) 0: jdbc:hive2://localhost:10000/default>   Against CDH5.8, same results. The big difference between the 2 releases is the time of execution. It consistently take 23-30 seconds on CDH5.3.10 where as it takes about 0.1 seconds on CDH5.8.0.   It is possible that my enviroment for CDH5.3.10 is not so kosher.   I do not have a CDH5.3.4 environment anymore. But let me know if you would like me to try it with CDH5.3.4.   Hope this helps.   ... View more