Member since
09-25-2015
112
Posts
88
Kudos Received
12
Solutions
My Accepted Solutions
Title | Views | Posted |
---|---|---|
9364 | 03-15-2017 03:17 PM | |
5833 | 02-17-2017 01:03 PM | |
1675 | 01-02-2017 10:47 PM | |
2497 | 11-16-2016 07:03 PM | |
1012 | 10-07-2016 05:24 PM |
05-19-2017
07:21 PM
2 Kudos
Hi @viswanath kammula. The Hive With clause/CTE (Common Table Expression) does not allow that type of nesting in its' syntax. In the documentation: https://cwiki.apache.org/confluence/display/Hive/Common+Table+Expression It states "The WITH clause is not supported within SubQuery Blocks." What you are trying to do could be accomplished (with a little different syntax) if you use a permanent view, or a temporary table. Also note: that same link, look at the bottom section titled "CTE in Views, CTAS, and Insert Statements." It shows you how to use CTEs (with clause) along with Views. The "View example" is similar to what yo want to do.
Here is a code example of a simple CTE/With statement (for the benefit of other people who will read this post):
with date_table as (select distinct year, quarter, month, dayofmonth
from flight_performance_orc_part
where yearmonth = '2016-12' limit 4)
select * from date_table d ; -- Or any other complex query, joins to other tables, etc.
Results:
year, quarter, month, dayofmonth
2016 4 12 1
2016 4 12 2
2016 4 12 3
2016 4 12 4
I hope this helps.
... View more
04-11-2017
12:28 PM
Hi @suresh kumar c. It was a long time ago, but yes I believe it did fix the query. Here is some quick checking to confirm: %0D is a carriage return in this chart: http://www.degraeve.com/reference/urlencoding.php and then I looked it up in this chart https://en.wikipedia.org/wiki/Escape_sequences_in_C and saw that \r = carriage return = %0D. Whatever your control character is, look it up and make sure it is URL-escaped when you try to drop the partition. I hope this helps.
... View more
04-10-2017
07:51 PM
Hi @Ciarán Porter. There is a hotfix required for this issue. In the meantime you can use the beeline cli and send the output to a csv format. A good explanation is found here: https://community.hortonworks.com/questions/25789/how-to-dump-the-output-from-beeline.html
... View more
04-10-2017
07:51 PM
Thanks @apappu. I will request the hotfix for the customer.
... View more
04-10-2017
07:51 PM
Update: This has been fixed in Ambari 2.5 (released April 4th, 2017). An added bonus is that Ambari 2.5 includes the new Hive View 2.0 - a vastly improved GUI for developing Hive apps. See attached picture:
... View more
04-10-2017
07:51 PM
2 Kudos
**** Update: This has been fixed in Ambari 2.5 (released April 4, 2017). See below for more details. **** This has been observed in Ambari 2.4 using HDP 2.5.0/HDP 2.5.3.4-5. Customer is having the issue and I am able to replicate it. Using the Ambari Hive view, I'm running a simple select query. By using tpcds data, it is easy to spot the problem since the customer table has an auto-number row_id. select c_customer_sk, c_customer_id, c_last_name from customer limit 204; The query runs successfully & the results page in the browser every 50 rows. The first page shows rows 1..50 the second page shows rows 51..100, but the 3rd page starts with row 102. Row 101 is skipped on the output. The 3rd page shows rows 102..151 The 4th page shows rows 152..201 but the 5th page starts with row 203. Row 202 is skipped on the output.
The same pattern is displayed when using "Save Results" -> "Download as CSV" - every 101st row is skipped. In Beeline when I run the same query - no issues. All rows are displayed. Have attempted to set the following values in Ambari but they don't affect the results: set hive.cli.print.header=true; set showHeader false (beeline) set headerInterval 0 Thanks in advance for the assistance.
... View more
Labels:
- Labels:
-
Apache Ambari
-
Apache Hive
03-15-2017
03:17 PM
There has been an update to the Support web page. Now the specific Begin and End Dates for Minor Releases (2.3, 2.4, 2.5, etc) are readily available. You can see when Full Support and Technical Guidance periods end. It's in the table at the bottom of this page: https://hortonworks.com/agreements/support-services-policy/
... View more
02-17-2017
01:03 PM
1 Kudo
To add the @Artem Ervits's solution - consider saving your complex query in the source RDBMS as a view. Then just call Sqoop specifying the view name instead of a table name. Makes the code nice and clean.
... View more
01-31-2017
08:40 PM
1 Kudo
@karthick baskaran I am not sure if you have the ability to use a 3rd Party tool, bit one of our trusted partners is Syncsort. If you've used the mainframe before you'll know who they are. Dealing with EBCDIC conversions, Copybooks, etc. are features that they excel at and provide in their flagship tool. It's called DMX-h and it would do what you need (in fact it can be your Data Integtration tool for all data, not just mainframe). http://www.syncsort.com/en/Products/BigData/DMXh
... View more