bq. One thing I wanted to highlight was my EVENT column datatype is VARCHAR....is that making EVENT column not to sort? Varchar field is also expected to be sorted in desc as like for other data types. Your "desc" declaration for Event column will be visible only when there are records having the same value for col1,col2,col3. you can order your keys as per your query pattern or create an additional index with a new order. ... View more

You need to move your snapshot from exported directory to the directory where hbase looks for snapshots(.hbase-snapshot). so that list_snapshots and other commands(like clone_snapshot ) can work easily hadoop dfs -mv hdfs://NAME_NODE:8020/hbase/.hbase-snapshot/<snapshot_name> hdfs://NAME_NODE:8020/apps/hbase/data/.hbase-snapshot/ hdfs dfs -mv hdfs://NAME_NODE:8020/hbase/archive/data/* hdfs://NAME_NODE:8020/apps/hbase/data/archive/data/ FYI:- to list snapshot directly from remote directory hbase org.apache.hadoop.hbase.snapshot.SnapshotInfo -remote-dir hdfs://NAME_NODE:8020/hbase/ -list-snapshots ... View more

Can you try something like this:- String querySql= "SELECT * FROM ENVI_DATA where PATIENT_ID=?"; RowMapper<EnviData> rowMapper = newRowMapper<EnviData>(){ @Override public EnviData mapRow(ResultSet rs,int rowNum) throwsSQLException{ EnviData data =new EnviData(); data.setPatientId(rs.getString(1)); data.setUserRoutes(rs.getArray(7).getArray()); } return jdbcTemplate.update(querySql, rowMapper, id); ... View more

If these are the only property you need from hbase-site.xml , you can create a connection by passing the properties in props. Properties props = new Properties(); props.setProperty(QueryServices.IS_NAMESPACE_MAPPING_ENABLED, Boolean.toString(true)); props.setProperty(QueryServices.IS_SYSTEM_TABLE_MAPPED_TO_NAMESPACE, Boolean.toString(false)); Connection conn = DriverManager.getConnection(getUrl(), props); ... View more

have you confirmed that "ER_V13_SPLIT_511_GZ_V2_METERKEY" doesn't appear when you do !tables on sqlline. (or you can query system.catalog if any link for this index exists) For a workaround:- you can try creating "ER_V13_SPLIT_511_GZ_V2_METERKEY" from HBase shell using same table descriptor of data table but with just different table name. ... View more

you can read the data from Phoenix using phoenix-spark connector and the using spark native APIs , you can join (or union) those data frames into single data frame. ... View more

Yeah, it seems like a bug that hint is ignored in the case of global functional index. As a workaround, you can include all columns in the covered index (I mean option#2). ... View more

Yeah, it seems like a bug that hint is ignored in the case of global functional index. As a workaround, you can include all columns in covered index (I mean option#2). ... View more

Select * is projecting all the columns, option1 suggest you can project only your pk columns and indexed column if you really want to use the non-covered global functional index. Ah, you mean, hint is also not helping. Let me check this at my side. ... View more

SELECT /*+ INDEX(S1.TABLE1 IDX_UPPER) */ * FROM S1.TABLE1 WHERE UPPER(FIRST_NAME) = 'ABC'; It looks like that you are trying to project all the columns of the table but your functional index is not covered(means does not include all the columns of the table). You have following options:- * Either project the pk columns and function select pk,pk2,pk3.., UPPER(FIRST_NAME) from S1.table1 * Or create a covered index. CREATE INDEX IDX_UPPER ON S1.TABLE1 (UPPER(FIRST_NAME)INCLUDE(COL1,COL2...) * Or if you are sure that you will have fewer rows for UPPER(FIRST_NAME) , then you can create non-covered local index.(this will fetch the remaining columns from data table automatically) CREATE LOCAL INDEX IDX_UPPER ON S1.TABLE1 (UPPER(FIRST_NAME) ... View more

Yes, MultipleOutputs doesn't make sense in combiner. As distribution of data among reducer is taken care by partioner class. ... View more

Is there a table showing how to compare different keyword in explain plans? Ex: “PARALLEL 1-WAY ROUND ROBIN FULL SCAN” versus “PARALLEL 9-WAY FULL SCAN”, which one would be faster if everything else was the same? https://phoenix.apache.org/tuning_guide.html Is there a way to determine if all of my RegionServers are being used during the execution of a particular query? Currently with explain plan ,there is no way to determine that all your regionservers are getting used for the query , although if there is full table scan ,then all the regions will be scanned but still you can't be sure that all regionservers are getting used as it depends upon your balancer policy and the distribution can be checked from master UI. What is the 9-CHUNK in “CLIENT 9-CHUNK 4352857 ROWS 943718447 BYTES” mean? Are more CHUNKs better or worse? https://phoenix.apache.org/tuning_guide.html What is the impact of using functions, such as HOUR(ts) on query execution time? Is the impact 1%, 10%, 50%, etc…? it depends , for eg:- if you are applying hour(ts) where 'ts' is the first part of the primary key and you don't have functional index on it, then the query may be turned into full table scan where the impact range could be anything. although, processing time of evaluating the function on the column value depends upon the function complexity and no. of rows you are scanning. ... View more

You can set hive.merge.mapredfiles to true , it will run the another mapreduce job after the original one to combine all files into one. https://cwiki.apache.org/confluence/display/Hive/AdminManual+Configuration ... View more

I think tracing is not yet part of HDP 2.4. but you can work with HortonWorks support to have a hot fix jar created for you. ... View more

You can increase phoenix timeout by setting high value for property "phoenix.query.timeoutMs" in hbase-site.xml . https://phoenix.apache.org/tuning.html ... View more

Table names seems to be different 'store_sales_4_1' and 'phx_store_sales_4_1' , is it a typo in your question? ... View more

As said in my last comment, you can adjust the timezone offset by setting "phoenix.query.dateFormatTimeZone" property in your hbase-site.xml of the client. https://phoenix.apache.org/tuning.html ... View more

You might be hitting similar problem stated in https://issues.apache.org/jira/browse/PHOENIX-3220 setting phoenix.query.dateFormatTimeZone to your local timezone can be workaround for now. ... View more

val conf = new SparkConf(); var sc = new SparkContext(conf) val sqlContext = new SQLContext(sc) val hbaseConfiguration= HBaseConfiguration.create() /** Table loaded from Phoenix without any predicate*/ val df1 = sqlContext.phoenixTableAsDataFrame("TABLE1", Array("ID", "COL1"), conf = hbaseConfiguration) df1.registerTempTable("sql_table_1") /** Table loaded from Phoenix with a predicate ID=1(filter will be pushed down to Phoenix) and only Predicate passed records will be loaded in spark */ val df2 = sqlContext.phoenixTableAsDataFrame("TABLE2", Array("ID", "TABLE1_ID"), predicate = Some("\"ID\" = 1"),conf = hbaseConfiguration) df2.registerTempTable("sql_table_2") /** Doing join with Spark (Not phoenix)*/ val sqlRdd = sqlContext.sql(""" |SELECT t1.ID, t1.COL1, t2.ID, t2.TABLE1_ID FROM sql_table_1 AS t1 |INNER JOIN sql_table_2 AS t2 ON (t2.TABLE1_ID = t1.ID)""".stripMargin ) /** using aggregate method of spark (not Phoenix))*/ val count = sqlRdd.count() ... View more

You can just pass the filter in Phoenix query and once data is loaded as RDD or DataFrame, you can use all the available aggregation method in spark for your use-case. ... View more

bq. Any idea how to make hive display this column for all the records? When you are adding long values through Java , declare them as long in hive to read. bq.I dont understand why the shell displays values in hex format (of binary value) for the numbers inserted through Java and values are displayed in ascii for records inserted through put command. Any reason? Because , when you do put from shell , it will always encode with string charset. And, scan displays string representation of the bytes without decoding them with the proper type because he doesn't know that whether your column is containing long value or string value. So, String value will be shown as it is but long has different encoding so String representation of bytes will appear in hex. //difference in encoding of String and long value public static byte[] toBytes(String s) { return s.getBytes(UTF8_CHARSET); } public static byte[] toBytes(longval) { byte [] b = new byte[8]; for (int i = 7; i > 0; i--) { b[i] = (byte) val; val >>>= 8; } b[0] = (byte) val; return b; } bq. How do I make the hbase shell display the values in proper ascii format using scan or get command in Hbase shell? 4. Even in Java code, I am unable to convert the result from Get method to string using Bytes.toString() it returns null whereas Bytes.toLong works in java code. Why is this difference In shell, you can't do but in Java , after doing Get, you need to do Bytes.toLong() to decode long value. ... View more

Stack trace doesn't seem to correspond to Phoenix v4.8.2. can you check version again? ... View more

do you have more stacktrace for the above exception? ... View more