Re: Extracting key word from a textual column
hashtag String column created from a semi automated process of >>>deriving it from account_table.transactiondescription >>> >>> Once the process is complete in populating the catalog table then we >>> just need to create a new DF based on join between catalog table and the >>> account_table. The join will use hashtag in catalog table to loop over >>> debit column in account_table for a given hashtag. That is pretty fast as >>> going through pattern matching is pretty intensive in any application and >>> database in real time. >>> >>> So one can build up the catalog table over time as a reference table. I >>> am sure such tables exist in commercial world. >>> >>> Anyway after getting results out I know how I am wasting my money on >>> different things, especially on clothing etc :) >>> >>> >>> HTH >>> >>> P.S. Also there is an issue with Spark not being able to read data >>> through Hive transactional tables that have not been compacted yet. Spark >>> just crashes. If these tables need to be updated regularly say catalog >>> table and they are pretty small, one might maintain them in an RDBMS and >>> read them once through JDBC into a DataFrame in Spark before doing >>> analytics. >>> >>> >>> Dr Mich Talebzadeh >>> >>> >>> >>> LinkedIn * >>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >>> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* >>> >>> >>> >>> http://talebzadehmich.wordpress.com >>> >>> >>> *Disclaimer:* Use it at your own risk. Any and all responsibility for >>> any loss, damage or destruction of data or any other property which may >>> arise from relying on this email's technical content is explicitly >>> disclaimed. The author will in no case be liable for any monetary damages >>> arising from such loss, damage or destruction. >>> >>> >>> >>> On 2 August 2016 at 17:56, Sonal Goyal <sonalgoy...@gmail.com> wrote: >>> >>>> Hi Mich, >>>> >>>> It seems like an entity resolution problem - looking at different >>>> representations of an entity - SAINSBURY in this case and matching them all >>>> together. How dirty is your data in the description - are there stop words >>>> like SACAT/SMKT etc you can strip off and get the base retailer entity ? >>>> >>>> Best Regards, >>>> Sonal >>>> Founder, Nube Technologies <http://www.nubetech.co> >>>> Reifier at Strata Hadoop World >>>> <https://www.youtube.com/watch?v=eD3LkpPQIgM> >>>> Reifier at Spark Summit 2015 >>>> <https://spark-summit.org/2015/events/real-time-fuzzy-matching-with-spark-and-elastic-search/> >>>> >>>> <http://in.linkedin.com/in/sonalgoyal> >>>> >>>> >>>> >>>> On Tue, Aug 2, 2016 at 9:55 PM, Mich Talebzadeh < >>>> mich.talebza...@gmail.com> wrote: >>>> >>>>> Thanks. >>>>> >>>>> I believe there is some catalog of companies that I can get and store >>>>> it in a table and math the company name to transactiondesciption column. >>>>> >>>>> That catalog should have sectors in it. For example company XYZ is >>>>> under Grocers etc which will make search and grouping much easier. >>>>> >>>>> I believe Spark can do it, though I am generally interested on >>>>> alternative ideas. >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> Dr Mich Talebzadeh >>>>> >>>>> >>>>> >>>>> LinkedIn * >>>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >>>>> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* >>>>> >>>>> >>>>> >>>>> http://talebzadehmich.wordpress.com >>>>> >>>>> >>>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for >>>>> any loss, damage or destruction of data or any other property which may >>>>> arise from relying on this email's technical content is explicitly >>>>> disclaimed. The
Re: Extracting key word from a textual column
If these tables need to be updated regularly say catalog >>>>> table and they are pretty small, one might maintain them in an RDBMS and >>>>> read them once through JDBC into a DataFrame in Spark before doing >>>>> analytics. >>>>> >>>>> >>>>> Dr Mich Talebzadeh >>>>> >>>>> LinkedIn >>>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >>>>> >>>>> http://talebzadehmich.wordpress.com >>>>> >>>>> Disclaimer: Use it at your own risk. Any and all responsibility for any >>>>> loss, damage or destruction of data or any other property which may arise >>>>> from relying on this email's technical content is explicitly disclaimed. >>>>> The author will in no case be liable for any monetary damages arising >>>>> from such loss, damage or destruction. >>>>> >>>>> >>>>>> On 2 August 2016 at 17:56, Sonal Goyal <sonalgoy...@gmail.com> wrote: >>>>>> Hi Mich, >>>>>> >>>>>> It seems like an entity resolution problem - looking at different >>>>>> representations of an entity - SAINSBURY in this case and matching them >>>>>> all together. How dirty is your data in the description - are there stop >>>>>> words like SACAT/SMKT etc you can strip off and get the base retailer >>>>>> entity ? >>>>>> >>>>>> Best Regards, >>>>>> Sonal >>>>>> Founder, Nube Technologies >>>>>> Reifier at Strata Hadoop World >>>>>> Reifier at Spark Summit 2015 >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>> On Tue, Aug 2, 2016 at 9:55 PM, Mich Talebzadeh >>>>>>> <mich.talebza...@gmail.com> wrote: >>>>>>> Thanks. >>>>>>> >>>>>>> I believe there is some catalog of companies that I can get and store >>>>>>> it in a table and math the company name to transactiondesciption column. >>>>>>> >>>>>>> That catalog should have sectors in it. For example company XYZ is >>>>>>> under Grocers etc which will make search and grouping much easier. >>>>>>> >>>>>>> I believe Spark can do it, though I am generally interested on >>>>>>> alternative ideas. >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> Dr Mich Talebzadeh >>>>>>> >>>>>>> LinkedIn >>>>>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >>>>>>> >>>>>>> http://talebzadehmich.wordpress.com >>>>>>> >>>>>>> Disclaimer: Use it at your own risk. Any and all responsibility for any >>>>>>> loss, damage or destruction of data or any other property which may >>>>>>> arise from relying on this email's technical content is explicitly >>>>>>> disclaimed. The author will in no case be liable for any monetary >>>>>>> damages arising from such loss, damage or destruction. >>>>>>> >>>>>>> >>>>>>>> On 2 August 2016 at 16:26, Yong Zhang <java8...@hotmail.com> wrote: >>>>>>>> Well, if you still want to use windows function for your logic, then >>>>>>>> you need to derive a new column out, like "catalog", and use it as >>>>>>>> part of grouping logic. >>>>>>>> >>>>>>>> >>>>>>>> Maybe you can use regex for deriving out this new column. The >>>>>>>> implementation needs to depend on your data in >>>>>>>> "transactiondescription", and regex gives you the most powerful way to >>>>>>>> handle your data. >>>>>>>> >>>>>>>> >>>>>>>> This is really not a Spark question, but how to you process your logic >>>>>>>> based on the data given. >>>>
Re: Extracting key word from a textual column
lytics. >>>> >>>> >>>> Dr Mich Talebzadeh >>>> >>>> LinkedIn >>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >>>> >>>> http://talebzadehmich.wordpress.com >>>> >>>> Disclaimer: Use it at your own risk. Any and all responsibility for any >>>> loss, damage or destruction of data or any other property which may arise >>>> from relying on this email's technical content is explicitly disclaimed. >>>> The author will in no case be liable for any monetary damages arising from >>>> such loss, damage or destruction. >>>> >>>> >>>>> On 2 August 2016 at 17:56, Sonal Goyal <sonalgoy...@gmail.com> wrote: >>>>> Hi Mich, >>>>> >>>>> It seems like an entity resolution problem - looking at different >>>>> representations of an entity - SAINSBURY in this case and matching them >>>>> all together. How dirty is your data in the description - are there stop >>>>> words like SACAT/SMKT etc you can strip off and get the base retailer >>>>> entity ? >>>>> >>>>> Best Regards, >>>>> Sonal >>>>> Founder, Nube Technologies >>>>> Reifier at Strata Hadoop World >>>>> Reifier at Spark Summit 2015 >>>>> >>>>> >>>>> >>>>> >>>>> >>>>>> On Tue, Aug 2, 2016 at 9:55 PM, Mich Talebzadeh >>>>>> <mich.talebza...@gmail.com> wrote: >>>>>> Thanks. >>>>>> >>>>>> I believe there is some catalog of companies that I can get and store it >>>>>> in a table and math the company name to transactiondesciption column. >>>>>> >>>>>> That catalog should have sectors in it. For example company XYZ is under >>>>>> Grocers etc which will make search and grouping much easier. >>>>>> >>>>>> I believe Spark can do it, though I am generally interested on >>>>>> alternative ideas. >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> Dr Mich Talebzadeh >>>>>> >>>>>> LinkedIn >>>>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >>>>>> >>>>>> http://talebzadehmich.wordpress.com >>>>>> >>>>>> Disclaimer: Use it at your own risk. Any and all responsibility for any >>>>>> loss, damage or destruction of data or any other property which may >>>>>> arise from relying on this email's technical content is explicitly >>>>>> disclaimed. The author will in no case be liable for any monetary >>>>>> damages arising from such loss, damage or destruction. >>>>>> >>>>>> >>>>>>> On 2 August 2016 at 16:26, Yong Zhang <java8...@hotmail.com> wrote: >>>>>>> Well, if you still want to use windows function for your logic, then >>>>>>> you need to derive a new column out, like "catalog", and use it as part >>>>>>> of grouping logic. >>>>>>> >>>>>>> >>>>>>> Maybe you can use regex for deriving out this new column. The >>>>>>> implementation needs to depend on your data in >>>>>>> "transactiondescription", and regex gives you the most powerful way to >>>>>>> handle your data. >>>>>>> >>>>>>> >>>>>>> This is really not a Spark question, but how to you process your logic >>>>>>> based on the data given. >>>>>>> >>>>>>> >>>>>>> Yong >>>>>>> >>>>>>> >>>>>>> >>>>>>> From: Mich Talebzadeh <mich.talebza...@gmail.com> >>>>>>> Sent: Tuesday, August 2, 2016 10:00 AM >>>>>>> To: user @spark >>>>>>> Subject: Extracting key word from a textual column >>>>>>> >>>>>>> Hi, >>>>>>> >>>>>>> Need some ideas. >>>>>>> &g
Re: Extracting key word from a textual column
all responsibility for >> any loss, damage or destruction of data or any other property which may >> arise from relying on this email's technical content is explicitly >> disclaimed. The author will in no case be liable for any monetary damages >> arising from such loss, damage or destruction. >> >> >> >> On 2 August 2016 at 17:56, Sonal Goyal <sonalgoy...@gmail.com> wrote: >> >>> Hi Mich, >>> >>> It seems like an entity resolution problem - looking at different >>> representations of an entity - SAINSBURY in this case and matching them all >>> together. How dirty is your data in the description - are there stop words >>> like SACAT/SMKT etc you can strip off and get the base retailer entity ? >>> >>> Best Regards, >>> Sonal >>> Founder, Nube Technologies <http://www.nubetech.co> >>> Reifier at Strata Hadoop World >>> <https://www.youtube.com/watch?v=eD3LkpPQIgM> >>> Reifier at Spark Summit 2015 >>> <https://spark-summit.org/2015/events/real-time-fuzzy-matching-with-spark-and-elastic-search/> >>> >>> <http://in.linkedin.com/in/sonalgoyal> >>> >>> >>> >>> On Tue, Aug 2, 2016 at 9:55 PM, Mich Talebzadeh < >>> mich.talebza...@gmail.com> wrote: >>> >>>> Thanks. >>>> >>>> I believe there is some catalog of companies that I can get and store >>>> it in a table and math the company name to transactiondesciption column. >>>> >>>> That catalog should have sectors in it. For example company XYZ is >>>> under Grocers etc which will make search and grouping much easier. >>>> >>>> I believe Spark can do it, though I am generally interested on >>>> alternative ideas. >>>> >>>> >>>> >>>> >>>> >>>> Dr Mich Talebzadeh >>>> >>>> >>>> >>>> LinkedIn * >>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >>>> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* >>>> >>>> >>>> >>>> http://talebzadehmich.wordpress.com >>>> >>>> >>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for >>>> any loss, damage or destruction of data or any other property which may >>>> arise from relying on this email's technical content is explicitly >>>> disclaimed. The author will in no case be liable for any monetary damages >>>> arising from such loss, damage or destruction. >>>> >>>> >>>> >>>> On 2 August 2016 at 16:26, Yong Zhang <java8...@hotmail.com> wrote: >>>> >>>>> Well, if you still want to use windows function for your logic, then >>>>> you need to derive a new column out, like "catalog", and use it as part of >>>>> grouping logic. >>>>> >>>>> >>>>> Maybe you can use regex for deriving out this new column. The >>>>> implementation needs to depend on your data in "transactiondescription", >>>>> and regex gives you the most powerful way to handle your data. >>>>> >>>>> >>>>> This is really not a Spark question, but how to you process your logic >>>>> based on the data given. >>>>> >>>>> >>>>> Yong >>>>> >>>>> >>>>> -- >>>>> *From:* Mich Talebzadeh <mich.talebza...@gmail.com> >>>>> *Sent:* Tuesday, August 2, 2016 10:00 AM >>>>> *To:* user @spark >>>>> *Subject:* Extracting key word from a textual column >>>>> >>>>> Hi, >>>>> >>>>> Need some ideas. >>>>> >>>>> *Summary:* >>>>> >>>>> I am working on a tool to slice and dice the amount of money I have >>>>> spent so far (meaning the whole data sample) on a given retailer so I have >>>>> a better idea of where I am wasting the money >>>>> >>>>> *Approach* >>>>> >>>>> Downloaded my bank statements from a given account in csv format from >>>>> inception till end of July. Read the data and stored it in ORC table. >>>>> >>>>> I am interested for a
Re: Extracting key word from a textual column
+1 > On Aug 2, 2016, at 2:29 PM, Jörn Franke <jornfra...@gmail.com> wrote: > > If you need to use single inserts, updates, deletes, select why not use hbase > with Phoenix? I see it as complementary to the hive / warehouse offering > >> On 02 Aug 2016, at 22:34, Mich Talebzadeh <mich.talebza...@gmail.com> wrote: >> >> Hi, >> >> I decided to create a catalog table in Hive ORC and transactional. That >> table has two columns of value >> >> transactiondescription === account_table.transactiondescription >> hashtag String column created from a semi automated process of deriving it >> from account_table.transactiondescription >> Once the process is complete in populating the catalog table then we just >> need to create a new DF based on join between catalog table and the >> account_table. The join will use hashtag in catalog table to loop over debit >> column in account_table for a given hashtag. That is pretty fast as going >> through pattern matching is pretty intensive in any application and database >> in real time. >> >> So one can build up the catalog table over time as a reference table. I am >> sure such tables exist in commercial world. >> >> Anyway after getting results out I know how I am wasting my money on >> different things, especially on clothing etc :) >> >> >> HTH >> >> P.S. Also there is an issue with Spark not being able to read data through >> Hive transactional tables that have not been compacted yet. Spark just >> crashes. If these tables need to be updated regularly say catalog table and >> they are pretty small, one might maintain them in an RDBMS and read them >> once through JDBC into a DataFrame in Spark before doing analytics. >> >> >> Dr Mich Talebzadeh >> >> LinkedIn >> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >> >> http://talebzadehmich.wordpress.com >> >> Disclaimer: Use it at your own risk. Any and all responsibility for any >> loss, damage or destruction of data or any other property which may arise >> from relying on this email's technical content is explicitly disclaimed. The >> author will in no case be liable for any monetary damages arising from such >> loss, damage or destruction. >> >> >>> On 2 August 2016 at 17:56, Sonal Goyal <sonalgoy...@gmail.com> wrote: >>> Hi Mich, >>> >>> It seems like an entity resolution problem - looking at different >>> representations of an entity - SAINSBURY in this case and matching them all >>> together. How dirty is your data in the description - are there stop words >>> like SACAT/SMKT etc you can strip off and get the base retailer entity ? >>> >>> Best Regards, >>> Sonal >>> Founder, Nube Technologies >>> Reifier at Strata Hadoop World >>> Reifier at Spark Summit 2015 >>> >>> >>> >>> >>> >>>> On Tue, Aug 2, 2016 at 9:55 PM, Mich Talebzadeh >>>> <mich.talebza...@gmail.com> wrote: >>>> Thanks. >>>> >>>> I believe there is some catalog of companies that I can get and store it >>>> in a table and math the company name to transactiondesciption column. >>>> >>>> That catalog should have sectors in it. For example company XYZ is under >>>> Grocers etc which will make search and grouping much easier. >>>> >>>> I believe Spark can do it, though I am generally interested on alternative >>>> ideas. >>>> >>>> >>>> >>>> >>>> >>>> Dr Mich Talebzadeh >>>> >>>> LinkedIn >>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >>>> >>>> http://talebzadehmich.wordpress.com >>>> >>>> Disclaimer: Use it at your own risk. Any and all responsibility for any >>>> loss, damage or destruction of data or any other property which may arise >>>> from relying on this email's technical content is explicitly disclaimed. >>>> The author will in no case be liable for any monetary damages arising from >>>> such loss, damage or destruction. >>>> >>>> >>>>> On 2 August 2016 at 16:26, Yong Zhang <java8...@hotmail.com> wrote: >>>>> Well, if you still want to use windows function for your logic, then you >>>>> need to derive a new colum
Re: Extracting key word from a textual column
Hi, I decided to create a catalog table in Hive ORC and transactional. That table has two columns of value 1. transactiondescription === account_table.transactiondescription 2. hashtag String column created from a semi automated process of deriving it from account_table.transactiondescription Once the process is complete in populating the catalog table then we just need to create a new DF based on join between catalog table and the account_table. The join will use hashtag in catalog table to loop over debit column in account_table for a given hashtag. That is pretty fast as going through pattern matching is pretty intensive in any application and database in real time. So one can build up the catalog table over time as a reference table. I am sure such tables exist in commercial world. Anyway after getting results out I know how I am wasting my money on different things, especially on clothing etc :) HTH P.S. Also there is an issue with Spark not being able to read data through Hive transactional tables that have not been compacted yet. Spark just crashes. If these tables need to be updated regularly say catalog table and they are pretty small, one might maintain them in an RDBMS and read them once through JDBC into a DataFrame in Spark before doing analytics. Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* http://talebzadehmich.wordpress.com *Disclaimer:* Use it at your own risk. Any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on this email's technical content is explicitly disclaimed. The author will in no case be liable for any monetary damages arising from such loss, damage or destruction. On 2 August 2016 at 17:56, Sonal Goyal <sonalgoy...@gmail.com> wrote: > Hi Mich, > > It seems like an entity resolution problem - looking at different > representations of an entity - SAINSBURY in this case and matching them all > together. How dirty is your data in the description - are there stop words > like SACAT/SMKT etc you can strip off and get the base retailer entity ? > > Best Regards, > Sonal > Founder, Nube Technologies <http://www.nubetech.co> > Reifier at Strata Hadoop World > <https://www.youtube.com/watch?v=eD3LkpPQIgM> > Reifier at Spark Summit 2015 > <https://spark-summit.org/2015/events/real-time-fuzzy-matching-with-spark-and-elastic-search/> > > <http://in.linkedin.com/in/sonalgoyal> > > > > On Tue, Aug 2, 2016 at 9:55 PM, Mich Talebzadeh <mich.talebza...@gmail.com > > wrote: > >> Thanks. >> >> I believe there is some catalog of companies that I can get and store it >> in a table and math the company name to transactiondesciption column. >> >> That catalog should have sectors in it. For example company XYZ is under >> Grocers etc which will make search and grouping much easier. >> >> I believe Spark can do it, though I am generally interested on >> alternative ideas. >> >> >> >> >> >> Dr Mich Talebzadeh >> >> >> >> LinkedIn * >> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* >> >> >> >> http://talebzadehmich.wordpress.com >> >> >> *Disclaimer:* Use it at your own risk. Any and all responsibility for >> any loss, damage or destruction of data or any other property which may >> arise from relying on this email's technical content is explicitly >> disclaimed. The author will in no case be liable for any monetary damages >> arising from such loss, damage or destruction. >> >> >> >> On 2 August 2016 at 16:26, Yong Zhang <java8...@hotmail.com> wrote: >> >>> Well, if you still want to use windows function for your logic, then you >>> need to derive a new column out, like "catalog", and use it as part of >>> grouping logic. >>> >>> >>> Maybe you can use regex for deriving out this new column. The >>> implementation needs to depend on your data in "transactiondescription", >>> and regex gives you the most powerful way to handle your data. >>> >>> >>> This is really not a Spark question, but how to you process your logic >>> based on the data given. >>> >>> >>> Yong >>> >>> >>> -- >>> *From:* Mich Talebzadeh <mich.talebza...@gmail.com> >>> *Sent:* Tuesday, Augu
Re: Extracting key word from a textual column
Hi Mich,
It seems like an entity resolution problem - looking at different
representations of an entity - SAINSBURY in this case and matching them all
together. How dirty is your data in the description - are there stop words
like SACAT/SMKT etc you can strip off and get the base retailer entity ?
Best Regards,
Sonal
Founder, Nube Technologies <http://www.nubetech.co>
Reifier at Strata Hadoop World <https://www.youtube.com/watch?v=eD3LkpPQIgM>
Reifier at Spark Summit 2015
<https://spark-summit.org/2015/events/real-time-fuzzy-matching-with-spark-and-elastic-search/>
<http://in.linkedin.com/in/sonalgoyal>
On Tue, Aug 2, 2016 at 9:55 PM, Mich Talebzadeh <mich.talebza...@gmail.com>
wrote:
> Thanks.
>
> I believe there is some catalog of companies that I can get and store it
> in a table and math the company name to transactiondesciption column.
>
> That catalog should have sectors in it. For example company XYZ is under
> Grocers etc which will make search and grouping much easier.
>
> I believe Spark can do it, though I am generally interested on alternative
> ideas.
>
>
>
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn *
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
> On 2 August 2016 at 16:26, Yong Zhang <java8...@hotmail.com> wrote:
>
>> Well, if you still want to use windows function for your logic, then you
>> need to derive a new column out, like "catalog", and use it as part of
>> grouping logic.
>>
>>
>> Maybe you can use regex for deriving out this new column. The
>> implementation needs to depend on your data in "transactiondescription",
>> and regex gives you the most powerful way to handle your data.
>>
>>
>> This is really not a Spark question, but how to you process your logic
>> based on the data given.
>>
>>
>> Yong
>>
>>
>> --
>> *From:* Mich Talebzadeh <mich.talebza...@gmail.com>
>> *Sent:* Tuesday, August 2, 2016 10:00 AM
>> *To:* user @spark
>> *Subject:* Extracting key word from a textual column
>>
>> Hi,
>>
>> Need some ideas.
>>
>> *Summary:*
>>
>> I am working on a tool to slice and dice the amount of money I have spent
>> so far (meaning the whole data sample) on a given retailer so I have a
>> better idea of where I am wasting the money
>>
>> *Approach*
>>
>> Downloaded my bank statements from a given account in csv format from
>> inception till end of July. Read the data and stored it in ORC table.
>>
>> I am interested for all bills that I paid using Debit Card (
>> transactiontype = "DEB") that comes out the account directly.
>> Transactiontype is the three character code lookup that I download as well.
>>
>> scala> ll_18740868.printSchema
>> root
>> |-- transactiondate: date (nullable = true)
>> |-- transactiontype: string (nullable = true)
>> |-- sortcode: string (nullable = true)
>> |-- accountnumber: string (nullable = true)
>> |-- transactiondescription: string (nullable = true)
>> |-- debitamount: double (nullable = true)
>> |-- creditamount: double (nullable = true)
>> |-- balance: double (nullable = true)
>>
>> The important fields are transactiondate, transactiontype,
>> transactiondescription and debitamount
>>
>> So using analytics. windowing I can do all sorts of things. For example
>> this one gives me the last time I spent money on retailer XYZ and the amount
>>
>> SELECT *
>> FROM (
>> select transactiondate, transactiondescription, debitamount
>> , rank() over (order by transactiondate desc) AS rank
>> from accounts.ll_18740868 where transactiondescription like '%XYZ%'
>> ) tmp
>> where rank <= 1
>>
>> And its equivalent using Windowing in FP
>>
>> import org.apache.spark.sql.expressions.Window
>> val wSpec =
>> Window.partitionBy("transactiontype").orderBy(desc("transactiondate"))
>> ll_18740868.filter(col("transactiondescription")
Re: Extracting key word from a textual column
Thanks.
I believe there is some catalog of companies that I can get and store it in
a table and math the company name to transactiondesciption column.
That catalog should have sectors in it. For example company XYZ is under
Grocers etc which will make search and grouping much easier.
I believe Spark can do it, though I am generally interested on alternative
ideas.
Dr Mich Talebzadeh
LinkedIn *
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
On 2 August 2016 at 16:26, Yong Zhang <java8...@hotmail.com> wrote:
> Well, if you still want to use windows function for your logic, then you
> need to derive a new column out, like "catalog", and use it as part of
> grouping logic.
>
>
> Maybe you can use regex for deriving out this new column. The
> implementation needs to depend on your data in "transactiondescription",
> and regex gives you the most powerful way to handle your data.
>
>
> This is really not a Spark question, but how to you process your logic
> based on the data given.
>
>
> Yong
>
>
> --
> *From:* Mich Talebzadeh <mich.talebza...@gmail.com>
> *Sent:* Tuesday, August 2, 2016 10:00 AM
> *To:* user @spark
> *Subject:* Extracting key word from a textual column
>
> Hi,
>
> Need some ideas.
>
> *Summary:*
>
> I am working on a tool to slice and dice the amount of money I have spent
> so far (meaning the whole data sample) on a given retailer so I have a
> better idea of where I am wasting the money
>
> *Approach*
>
> Downloaded my bank statements from a given account in csv format from
> inception till end of July. Read the data and stored it in ORC table.
>
> I am interested for all bills that I paid using Debit Card (
> transactiontype = "DEB") that comes out the account directly.
> Transactiontype is the three character code lookup that I download as well.
>
> scala> ll_18740868.printSchema
> root
> |-- transactiondate: date (nullable = true)
> |-- transactiontype: string (nullable = true)
> |-- sortcode: string (nullable = true)
> |-- accountnumber: string (nullable = true)
> |-- transactiondescription: string (nullable = true)
> |-- debitamount: double (nullable = true)
> |-- creditamount: double (nullable = true)
> |-- balance: double (nullable = true)
>
> The important fields are transactiondate, transactiontype,
> transactiondescription and debitamount
>
> So using analytics. windowing I can do all sorts of things. For example
> this one gives me the last time I spent money on retailer XYZ and the amount
>
> SELECT *
> FROM (
> select transactiondate, transactiondescription, debitamount
> , rank() over (order by transactiondate desc) AS rank
> from accounts.ll_18740868 where transactiondescription like '%XYZ%'
> ) tmp
> where rank <= 1
>
> And its equivalent using Windowing in FP
>
> import org.apache.spark.sql.expressions.Window
> val wSpec =
> Window.partitionBy("transactiontype").orderBy(desc("transactiondate"))
> ll_18740868.filter(col("transactiondescription").contains("XYZ")).select($"transactiondate",$"transactiondescription",
> rank().over(wSpec).as("rank")).filter($"rank"===1).show
>
>
> +---+--++
> |transactiondate|transactiondescription|rank|
> +---+--++
> | 2015-12-15| XYZ LTD CD 4636 | 1|
> +---+--++
>
> So far so good. But if I want to find all I spent on each retailer, then
> it gets trickier as a retailer appears like below in the column
> transactiondescription:
>
>
> ll_18740868.where($"transactiondescription".contains("SAINSBURY")).select($"transactiondescription").show(5)
> +--+
> |transactiondescription|
> +--+
> | SAINSBURYS SMKT C...|
> | SACAT SAINSBURYS ...|
> | SAINSBURY'S SMKT ...|
> | SAINSBURYS S/MKT ...|
> | SACAT SAINSBURYS ...|
> +--+
>
> If I look at them I know they all belong to SAINBURYS (food retailer). I
> have done some crude grouping and it works someh
Re: Extracting key word from a textual column
Well, if you still want to use windows function for your logic, then you need
to derive a new column out, like "catalog", and use it as part of grouping
logic.
Maybe you can use regex for deriving out this new column. The implementation
needs to depend on your data in "transactiondescription", and regex gives you
the most powerful way to handle your data.
This is really not a Spark question, but how to you process your logic based on
the data given.
Yong
From: Mich Talebzadeh <mich.talebza...@gmail.com>
Sent: Tuesday, August 2, 2016 10:00 AM
To: user @spark
Subject: Extracting key word from a textual column
Hi,
Need some ideas.
Summary:
I am working on a tool to slice and dice the amount of money I have spent so
far (meaning the whole data sample) on a given retailer so I have a better idea
of where I am wasting the money
Approach
Downloaded my bank statements from a given account in csv format from inception
till end of July. Read the data and stored it in ORC table.
I am interested for all bills that I paid using Debit Card ( transactiontype =
"DEB") that comes out the account directly. Transactiontype is the three
character code lookup that I download as well.
scala> ll_18740868.printSchema
root
|-- transactiondate: date (nullable = true)
|-- transactiontype: string (nullable = true)
|-- sortcode: string (nullable = true)
|-- accountnumber: string (nullable = true)
|-- transactiondescription: string (nullable = true)
|-- debitamount: double (nullable = true)
|-- creditamount: double (nullable = true)
|-- balance: double (nullable = true)
The important fields are transactiondate, transactiontype,
transactiondescription and debitamount
So using analytics. windowing I can do all sorts of things. For example this
one gives me the last time I spent money on retailer XYZ and the amount
SELECT *
FROM (
select transactiondate, transactiondescription, debitamount
, rank() over (order by transactiondate desc) AS rank
from accounts.ll_18740868 where transactiondescription like '%XYZ%'
) tmp
where rank <= 1
And its equivalent using Windowing in FP
import org.apache.spark.sql.expressions.Window
val wSpec =
Window.partitionBy("transactiontype").orderBy(desc("transactiondate"))
ll_18740868.filter(col("transactiondescription").contains("XYZ")).select($"transactiondate",$"transactiondescription",
rank().over(wSpec).as("rank")).filter($"rank"===1).show
+---+--++
|transactiondate|transactiondescription|rank|
+---+--++
| 2015-12-15| XYZ LTD CD 4636 | 1|
+---+--++
So far so good. But if I want to find all I spent on each retailer, then it
gets trickier as a retailer appears like below in the column
transactiondescription:
ll_18740868.where($"transactiondescription".contains("SAINSBURY")).select($"transactiondescription").show(5)
+--+
|transactiondescription|
+--+
| SAINSBURYS SMKT C...|
| SACAT SAINSBURYS ...|
| SAINSBURY'S SMKT ...|
| SAINSBURYS S/MKT ...|
| SACAT SAINSBURYS ...|
+--+
If I look at them I know they all belong to SAINBURYS (food retailer). I have
done some crude grouping and it works somehow
//define UDF here to handle substring
val SubstrUDF = udf { (s: String, start: Int, end: Int) => s.substring(start,
end) }
var cutoff = "CD" // currently used in the statement
val wSpec2 =
Window.partitionBy(SubstrUDF($"transactiondescription",lit(0),instr($"transactiondescription",
cutoff)-1))
ll_18740868.where($"transactiontype" === "DEB" &&
($"transactiondescription").isNotNull).select(SubstrUDF($"transactiondescription",lit(0),instr($"transactiondescription",
cutoff)-1).as("Retailer"),sum($"debitamount").over(wSpec2).as("Spent")).distinct.orderBy($"Spent").collect.foreach(println)
However, I really need to extract the "keyword" retailer name from
transactiondescription column And I need some ideas about the best way of doing
it. Is this possible in Spark?
Thanks
Dr Mich Talebzadeh
LinkedIn
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
http://talebzadehmich.wordpress.com
Disclaimer: Use it at your own risk. Any and all responsibility for any loss,
damage or destruction of data or any other property which may arise from
relying on this email's technical content is explicitly disclaimed. The author
will in no case be liable for any monetary damages arising from such loss,
damage or destruction.
Extracting key word from a textual column
Hi,
Need some ideas.
*Summary:*
I am working on a tool to slice and dice the amount of money I have spent
so far (meaning the whole data sample) on a given retailer so I have a
better idea of where I am wasting the money
*Approach*
Downloaded my bank statements from a given account in csv format from
inception till end of July. Read the data and stored it in ORC table.
I am interested for all bills that I paid using Debit Card (
transactiontype = "DEB") that comes out the account directly.
Transactiontype is the three character code lookup that I download as well.
scala> ll_18740868.printSchema
root
|-- transactiondate: date (nullable = true)
|-- transactiontype: string (nullable = true)
|-- sortcode: string (nullable = true)
|-- accountnumber: string (nullable = true)
|-- transactiondescription: string (nullable = true)
|-- debitamount: double (nullable = true)
|-- creditamount: double (nullable = true)
|-- balance: double (nullable = true)
The important fields are transactiondate, transactiontype,
transactiondescription and debitamount
So using analytics. windowing I can do all sorts of things. For example
this one gives me the last time I spent money on retailer XYZ and the amount
SELECT *
FROM (
select transactiondate, transactiondescription, debitamount
, rank() over (order by transactiondate desc) AS rank
from accounts.ll_18740868 where transactiondescription like '%XYZ%'
) tmp
where rank <= 1
And its equivalent using Windowing in FP
import org.apache.spark.sql.expressions.Window
val wSpec =
Window.partitionBy("transactiontype").orderBy(desc("transactiondate"))
ll_18740868.filter(col("transactiondescription").contains("XYZ")).select($"transactiondate",$"transactiondescription",
rank().over(wSpec).as("rank")).filter($"rank"===1).show
+---+--++
|transactiondate|transactiondescription|rank|
+---+--++
| 2015-12-15| XYZ LTD CD 4636 | 1|
+---+--++
So far so good. But if I want to find all I spent on each retailer, then it
gets trickier as a retailer appears like below in the column
transactiondescription:
ll_18740868.where($"transactiondescription".contains("SAINSBURY")).select($"transactiondescription").show(5)
+--+
|transactiondescription|
+--+
| SAINSBURYS SMKT C...|
| SACAT SAINSBURYS ...|
| SAINSBURY'S SMKT ...|
| SAINSBURYS S/MKT ...|
| SACAT SAINSBURYS ...|
+--+
If I look at them I know they all belong to SAINBURYS (food retailer). I
have done some crude grouping and it works somehow
//define UDF here to handle substring
val SubstrUDF = udf { (s: String, start: Int, end: Int) =>
s.substring(start, end) }
var cutoff = "CD" // currently used in the statement
val wSpec2 =
Window.partitionBy(SubstrUDF($"transactiondescription",lit(0),instr($"transactiondescription",
cutoff)-1))
ll_18740868.where($"transactiontype" === "DEB" &&
($"transactiondescription").isNotNull).select(SubstrUDF($"transactiondescription",lit(0),instr($"transactiondescription",
cutoff)-1).as("Retailer"),sum($"debitamount").over(wSpec2).as("Spent")).distinct.orderBy($"Spent").collect.foreach(println)
However, I really need to extract the "keyword" retailer name from
transactiondescription column And I need some ideas about the best way of
doing it. Is this possible in Spark?
Thanks
Dr Mich Talebzadeh
LinkedIn *
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
