Hi Pirinthapan,
I can see that you have tried to group by the relavant fields which is
correct. I am not sure whether you have tried to group by later on after
running the hive script at least once. Otherwise it is just basic sql and
it should work.
You can try dropping the hive intermediate hive table by running "drop
table AppCommits" as a query in BAM. Run the aforesaid hive script again
and see the result.
Regards
Gayan
On Tue, Sep 16, 2014 at 6:52 PM, Pirinthapan Mahendran <pirintha...@wso2.com
> wrote:
> Hi Gayan,
>
> I have written the following hive script to collect the data from
> Cassandra.
>
> CREATE EXTERNAL TABLE IF NOT EXISTS AppCommit(row_id STRING,
> applicationKey STRING, tenantDomain STRING, user STRING, revision STRING,
> timeSt BIGINT)
> STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler' with
> SERDEPROPERTIES("wso2.carbon.datasource.name" =
> "WSO2BAM_CASSANDRA_DATASOURCE",
> "cassandra.cf.name" = "org_wso2_carbon_appfactory_appCommit" ,
> "cassandra.columns.mapping" = ":key, payload_appKey,
> payload_tenantDomain, payload_user, payload_revision, Timestamp");
>
>
> CREATE EXTERNAL TABLE IF NOT EXISTS commit_test1 (TENANT_DOMAIN STRING,
> APPLICATION_KEY STRING, DAY STRING, COMMITS BIGINT) STORED BY
> 'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler' TBLPROPERTIES
> ( 'mapred.jdbc.driver.class' = 'com.mysql.jdbc.Driver' , '
> wso2.carbon.datasource.name'='WSO2_AF_STAT_DB','hive.jdbc.update.on.duplicate'
> = 'true', 'hive.jdbc.primary.key.fields' =
> 'TENANT_DOMAIN,APPLICATION_KEY,DAY,COMMITS', 'hive.jdbc.table.create.query'
> = 'CREATE TABLE commit_test1(APPLICATION_KEY VARCHAR(100), TENANT_DOMAIN
> VARCHAR(100), DAY VARCHAR(100), COMMITS BIGINT)');
>
>
> INSERT OVERWRITE TABLE commit_test1
> SELECT rslt.tenantDomain, rslt.applicationKey, rslt.day,
> count(rslt.applicationKey) as COMMITS FROM(SELECT tenantDomain,
> applicationKey, day(from_unixtime(cast(timest/1000 as BIGINT),'yyyy-MM-dd
> HH:mm:ss.SSS' )) day FROM AppCommit WHERE (UNIX_TIMESTAMP() -
> cast(timest/1000 as BIGINT)) < 1209600) rslt GROUP BY rslt.tenantDomain,
> rslt.applicationKey, rslt.day;
>
>
> when I execute this script it insert the following data to mysql database.
>
>
>
> In the above table when the commit number increases, the new data is
> appended to the table as in the last four rows. But my requirement is to
> have only one entry for each combination of APPLICATION_KEY, TENANT_DOMAIN
> and DAY.
>
> So could you please tell me whether there are any methods to do this using
> Hive?
>
> Thanks.
>
>
>
> Mahendran Pirinthapan
> Software Engineer | WSO2 Inc.
> Mobile +94772378732.
>
> On Mon, Sep 15, 2014 at 10:05 PM, Pirinthapan Mahendran <
> pirintha...@wso2.com> wrote:
>
>> Thanks Gayan. Now its working.
>>
>>
>>
>> Mahendran Pirinthapan
>> Software Engineer | WSO2 Inc.
>> Mobile +94772378732.
>>
>> On Mon, Sep 15, 2014 at 9:40 PM, Gayan Dhanushka <gay...@wso2.com> wrote:
>>
>>> Hi Pirinthapan,
>>>
>>> What you have done is correct. But in order to publish commit details to
>>> BAM from the gitblit server you need to enable data publishing in the
>>> gitblit server.
>>>
>>> Please locate and open the gitblit.properties file resides within
>>> <GITBLIT_HOME>/data folder. There is a property at the bottom called
>>> "publishToBAM". You need to assign true to this property and restart the
>>> server.
>>>
>>> This should solve your problem.
>>>
>>> Thanks
>>> Gayan
>>>
>>>
>>> On Mon, Sep 15, 2014 at 9:25 PM, Pirinthapan Mahendran <
>>> pirintha...@wso2.com> wrote:
>>>
>>>> Hi Gayan,
>>>>
>>>> Now I'm writing Hive script to collect the necessary commit data from
>>>> Cassandra to MYSQL. Before I started to write this I tested the existing
>>>> hive script with the name "AppCommits" in BAM server. For this I did
>>>> the following steps:
>>>>
>>>> 1. I created an application in App Factory.
>>>> 2. Cloned the application to my PC.
>>>> 3. Made some changes and did some commits.
>>>> 4. Then I accessed the GitBlit server in the browser. It shows all the
>>>> commit details.
>>>> 5. Then I executed the hive script "AppCommits" from BAM management
>>>> console.
>>>> 6. Then I checked the mysql database. The table "APP_COMMITS" is there.
>>>> 7. But when I did some queries from that table, it returns empty set.
>>>>
>>>> According to my understanding whenever a commit happens the commit data
>>>> will be published to the BAM server by the script [1].
>>>>
>>>> Could you please make me clear about whether my understandings and the
>>>> procedures, I did, are correct. If those are correct could you please help
>>>> me to find out, why the data is not in mysql.
>>>>
>>>> 1.
>>>> https://svn.wso2.org/repos/wso2/scratch/appfactory_2.0.0/products/appfactory/2.0.1/vmware/appfactory_deployment_s4/resources/configs/jenkins.groovy
>>>>
>>>> Thanks.
>>>>
>>>>
>>>>
>>>> Mahendran Pirinthapan
>>>> Software Engineer | WSO2 Inc.
>>>> Mobile +94772378732.
>>>>
>>>> On Thu, Sep 11, 2014 at 1:21 PM, Iqbal Irham <iq...@wso2.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> Update on the project below.
>>>>>
>>>>> We have showed the hardcoded diagrams(bar chart for commit logs and
>>>>> pie chat for build statistics) to Dimuthu and Ajanthan and got their
>>>>> feedback.
>>>>>
>>>>> According to that,
>>>>>
>>>>> 1. Application dashboard will have commit logs bar chart with last
>>>>> 2 week data.
>>>>> 2. Detail page will have a detail bar chart for commit logs and a
>>>>> pie chart for build statistics.
>>>>> 3. Both graphs will have options to choose the time period of the
>>>>> graph.
>>>>> 4. Detail page bar chart will have zooming option to get the
>>>>> accurate value of the column.
>>>>>
>>>>>
>>>>> Thanks,
>>>>>
>>>>> On Tue, Sep 9, 2014 at 6:05 PM, Vimalanathan Rajeevan <
>>>>> rajeev...@wso2.com> wrote:
>>>>>
>>>>>> Hi Pirinthapan,
>>>>>>
>>>>>> I would like to give some feedbacks on your design,
>>>>>> [1] Giving boarders for each graphs and tables
>>>>>> [2] You can increase the font size for headings (ex. "Build
>>>>>> Statistics", "Weekly commit logs")
>>>>>> [3] Bring the contents of browser to central position without align
>>>>>> it to left [1]
>>>>>> [4] Give some spaces at top before starting contents
>>>>>> [4] You can use more attractive 3D graph designing tools
>>>>>>
>>>>>> Best Regards,
>>>>>> V.Rajeevan
>>>>>> Software Engineer,
>>>>>> WSO2 Inc. :http://wso2.com
>>>>>>
>>>>>> Mobile : +94 773090875
>>>>>> Email : rajeev...@wso2.com
>>>>>>
>>>>>> On Sat, Sep 6, 2014 at 1:04 PM, Pirinthapan Mahendran <
>>>>>> pirintha...@wso2.com> wrote:
>>>>>>
>>>>>>> Hi All,
>>>>>>>
>>>>>>> We have come up with a mockup design for showing the 'commit logs
>>>>>>> statistics' [1] and 'build statistics' [2] in App Factory. We would
>>>>>>> like to
>>>>>>> have your feed backs on these designs.
>>>>>>>
>>>>>>> 1.
>>>>>>> https://www.justinmind.com/usernote/tests/12597285/12597288/12597302/index.html#/screens/d12245cc-1680-458d-89dd-4f0d7fb22724
>>>>>>>
>>>>>>> 2.
>>>>>>> https://www.justinmind.com/usernote/tests/12597285/12597288/12597486/index.html#/screens/d12245cc-1680-458d-89dd-4f0d7fb22724
>>>>>>>
>>>>>>> Thanks.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Mahendran Pirinthapan
>>>>>>> Software Engineer | WSO2 Inc.
>>>>>>> Mobile +94772378732.
>>>>>>>
>>>>>>>
>>>>>>> On Fri, Sep 5, 2014 at 9:38 AM, Gayan Dhanushka <gay...@wso2.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hi Iqbal,
>>>>>>>>
>>>>>>>> Currently we have START, SUCCESS and FAIL status. If you closely
>>>>>>>> have a look you can see that *appBuildStream *is a private
>>>>>>>> variable which defines a data stream, not a method. For each of these
>>>>>>>> data
>>>>>>>> stream definitions, there are publish methods in the
>>>>>>>> BamDataPublisher.java
>>>>>>>> class.
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>> Gayan
>>>>>>>>
>>>>>>>>
>>>>>>>> On Thu, Sep 4, 2014 at 2:22 PM, Iqbal Irham <iq...@wso2.com> wrote:
>>>>>>>>
>>>>>>>>> Hi Gayan,
>>>>>>>>>
>>>>>>>>> Thank you very much for the reply.
>>>>>>>>> We are in the process of identifying the Build statuses which we
>>>>>>>>> are going to get from BAM.
>>>>>>>>> In the java code(BamDataPublisher.java) i found a method
>>>>>>>>> *appBuildStream.*
>>>>>>>>> As i highlighted below there is a String for Status.
>>>>>>>>> Please help us to find out what are the status values(i.e
>>>>>>>>> success,fail,Pending,Aborted) it's publishing.
>>>>>>>>>
>>>>>>>>> 'payloadData':["+
>>>>>>>>> " {'name':'applicationName','type':'string'},"+
>>>>>>>>> " {'name':'applicationKey','type':'string'},"+
>>>>>>>>> " {'name':'applicationVersion','type':'string'},"+
>>>>>>>>> " {'name':'timeStamp','type':'double'},"+
>>>>>>>>> " {'name':'tenantId', 'type':'string'},"+
>>>>>>>>> " *{'name':'status', 'type':'string'}*,"+
>>>>>>>>> " {'name':'buildId', 'type':'string'},"+
>>>>>>>>> " {'name':'revision', 'type':'string'},"+
>>>>>>>>> " {'name':'user', 'type':'string' }"+
>>>>>>>>> " ];
>>>>>>>>>
>>>>>>>>> Thanks
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Thu, Sep 4, 2014 at 1:57 PM, Gayan Dhanushka <gay...@wso2.com>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> Hi Pirinthapan,
>>>>>>>>>>
>>>>>>>>>> I believe you want to visualize the number of builds and commits
>>>>>>>>>> in the per each day inside an application in App Factory. For this we
>>>>>>>>>> already collect the build and commit data. We collect the build data
>>>>>>>>>> through App Factory and commits data through a git post commit hook
>>>>>>>>>> [1].
>>>>>>>>>> Please note that we have multiple activity points in App Factory
>>>>>>>>>> from where
>>>>>>>>>> the events are published to BAM. So the data that you are looking
>>>>>>>>>> for is
>>>>>>>>>> already being published.
>>>>>>>>>>
>>>>>>>>>> [1]
>>>>>>>>>> https://svn.wso2.org/repos/wso2/scratch/appfactory_2.0.0/products/appfactory/2.0.1/vmware/appfactory_deployment_s4/resources/configs/jenkins.groovy
>>>>>>>>>>
>>>>>>>>>> Regards
>>>>>>>>>> Gayan
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Thu, Sep 4, 2014 at 11:11 AM, Pirinthapan Mahendran <
>>>>>>>>>> pirintha...@wso2.com> wrote:
>>>>>>>>>>
>>>>>>>>>>> Hi Gayan,
>>>>>>>>>>>
>>>>>>>>>>> We went through the 'BamDataPublisher' class [1]. In this class
>>>>>>>>>>> the publish events are available for App creation, App version,
>>>>>>>>>>> Tenant
>>>>>>>>>>> user, App user, App issue and App build. But we need the data for
>>>>>>>>>>> 'Commit
>>>>>>>>>>> logs' as well. So do we need to create an event to publish these
>>>>>>>>>>> commit
>>>>>>>>>>> logs?
>>>>>>>>>>>
>>>>>>>>>>> 1.
>>>>>>>>>>> https://svn.wso2.org/repos/wso2/scratch/appfactory_2.0.0/components/appfac/org.wso2.carbon.appfactory.bam.integration/2.0.1/src/main/java/org/wso2/carbon/appfactory/bam/integration/BamDataPublisher.java
>>>>>>>>>>>
>>>>>>>>>>> Thanks.
>>>>>>>>>>>
>>>>>>>>>>> Mahendran Pirinthapan
>>>>>>>>>>> Software Engineer | WSO2 Inc.
>>>>>>>>>>> Mobile +94772378732.
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> Gayan Dhanuska
>>>>>>>>>> Software Engineer
>>>>>>>>>> http://wso2.com/
>>>>>>>>>> Lean Enterprise Middleware
>>>>>>>>>>
>>>>>>>>>> Mobile
>>>>>>>>>> 071 666 2327
>>>>>>>>>>
>>>>>>>>>> Office
>>>>>>>>>> Tel : 94 11 214 5345
>>>>>>>>>> Fax : 94 11 214 5300
>>>>>>>>>>
>>>>>>>>>> Twitter : https://twitter.com/gayanlggd
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Irham Iqbal
>>>>>>>>> Software Engineer - Test Automation
>>>>>>>>> WSO2, Inc.: http://wso2.com
>>>>>>>>> lean. enterprise. middleware
>>>>>>>>> phone: +94 777888452
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Gayan Dhanuska
>>>>>>>> Software Engineer
>>>>>>>> http://wso2.com/
>>>>>>>> Lean Enterprise Middleware
>>>>>>>>
>>>>>>>> Mobile
>>>>>>>> 071 666 2327
>>>>>>>>
>>>>>>>> Office
>>>>>>>> Tel : 94 11 214 5345
>>>>>>>> Fax : 94 11 214 5300
>>>>>>>>
>>>>>>>> Twitter : https://twitter.com/gayanlggd
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> Dev mailing list
>>>>>>> Dev@wso2.org
>>>>>>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>>>>>>
>>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> Dev mailing list
>>>>>> Dev@wso2.org
>>>>>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Irham Iqbal
>>>>> Software Engineer - Test Automation
>>>>> WSO2, Inc.: http://wso2.com
>>>>> lean. enterprise. middleware
>>>>> phone: +94 777888452
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Gayan Dhanuska
>>> Software Engineer
>>> http://wso2.com/
>>> Lean Enterprise Middleware
>>>
>>> Mobile
>>> 071 666 2327
>>>
>>> Office
>>> Tel : 94 11 214 5345
>>> Fax : 94 11 214 5300
>>>
>>> Twitter : https://twitter.com/gayanlggd
>>>
>>
>>
>
--
Gayan Dhanuska
Software Engineer
http://wso2.com/
Lean Enterprise Middleware
Mobile
071 666 2327
Office
Tel : 94 11 214 5345
Fax : 94 11 214 5300
Twitter : https://twitter.com/gayanlggd
_______________________________________________
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev
