Defining Global variables

[Anji]

Hi,

We would like to have a variable/collection that can be used inside
all queries,scripts.If JEXL is the solution how to declare the
variables/collections and Intialize them with the results of the
scriptella query.

Please provide any example code.
Any documents for JEXL usage inside scriptella would be very help
full.

We could not find any info regarding declaring variable at the link
http://commons.apache.org/jexl/reference/syntax.html#Literals.

Thanks.

[Kirill Volgin]

Hi Anji,

This link should help : http://scriptella.javaforge.com/docs/api/scriptella/driver/jexl/package-summary.html#package_description

You may also search this group or obsolete forum http://www.javaforge.com/proj/forum/browseForum.do?forum_id=3126 

--
You received this message because you are subscribed to the Google Groups "Scriptella ETL" group.
To post to this group, send email to scrip...@googlegroups.com.
To unsubscribe from this group, send email to scriptella+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/scriptella?hl=en.

--
Sincerely,
Kirill Volgin.

[Fyodor Kupolov]

Hi guys,

In addition to Kirill's links. For Scriptella 1.1 I've added support
for global variables via etl.globals['varName'] syntax. Example:

<!-- Set number of records as a global variable -->
<query connection-id="db">
select count(id) as c from Errors
<script connection-id="jexl">
etl.globals['errorsCount'] = c;
<script>
</query>

<!-- Then reuse this variable in other parts of ETL file -->
<script connection-id="text" if="etl.globals['errorsCount'] gt 0">
${etl.globals['errorsCount'])}
</script>

I'll publish a blog post with more explanations pretty soon. Just one
note, try to avoid using global variables when an alternative solution
exists. Latest 1.1 snapshot available at http://www.javaforge.com/proj/doc.do?doc_id=8354

Regards,
Fyodor

[Fyodor Kupolov]

As promised, here is the link to the post describing global variables
in more details: http://scriptella.wordpress.com/2010/08/22/new-feature-global-variables/

On Aug 20, 11:00 pm, Fyodor Kupolov <kupo...@gmail.com> wrote:
> Hi guys,
>
> In addition to Kirill's links. For Scriptella 1.1 I've added support
> for global variables via etl.globals['varName'] syntax. Example:
>
>     <!-- Set number of records as a global variable -->
>     <query connection-id="db">
>         select count(id) as c from Errors
>         <script connection-id="jexl">
>             etl.globals['errorsCount'] = c;
>         <script>
>     </query>
>
>     <!-- Then reuse this variable in other parts of ETL file -->
>     <script connection-id="text" if="etl.globals['errorsCount'] gt 0">
>         ${etl.globals['errorsCount'])}
>     </script>
>
> I'll publish a blog post with more explanations pretty soon. Just one
> note, try to avoid using global variables when an alternative solution

> exists. Latest 1.1 snapshot available athttp://www.javaforge.com/proj/doc.do?doc_id=8354

[Anji]

Hi Fyodor,

Thank You Very much for the detailed explanation. i am using
scriptella 1.0. i am getting erros when i use the array declaration
array1 = [0];
Could u please proved us one example that shows how to use JEXL array/
map inside scriptella. for example reading a file and loading into
array.

Error:
Driver exception: org.apache.commons.jexl.parser.ParseException:
Encountered "[" at line 1, column 10.
Was expecting one of:
<INTEGER_LITERAL> ...
<FLOAT_LITERAL> ...
"empty" ...


With Thanks,
Anji.

[Anji]

Hi Fyodor,

The reason that is driving us for the global collection is we would
like to write all the rows of a query to a global collection instead
to a file and use it in other scripts. i am not sure whether it is a
scallable option but would like to try to increase performance.
please provide an example with a JEXL global collection.

With Thanks,
Anji.


On Aug 23, 3:31 am, Fyodor Kupolov <kupo...@gmail.com> wrote:

[Fyodor Kupolov]

Hi Anji,

Thanks for pointing out this problem. I've forgotten that JEXL1.1 even
did not allow to create arrays. I've updated the post with links to
the FAQ and Janino example:
FAQ for 1.0 - http://scriptella.javaforge.com/faq.html#SET_VARIABLE
Updated part of the post with Janino example:
http://scriptella.wordpress.com/2010/08/22/new-feature-global-variables/#update1

[Fyodor Kupolov]

I'm not sure if storing intermediate results in memory is a good
solution even in terms of performance.This really depends on the
actual scenario, but I would consider the following options:
1) The most important thing is to understand where do you have a
bottleneck, is this a slow datastore you are querying or you have to
query it too many times. A scalable ETL procedure should not store
intermediate data at all, it should connect the datasources by
querying and directly inserting, thus avoiding intermediate storage.
2) If the query is expensive to execute, but returns a reasonable
amount of records, less than million records for example. I would
create a temp table in the target database and store results in it.
Most commercial DB handle caching of data in tables in a much more
efficient way than implementing a memory store by your own. I would
strongly recommend to use facilities provided by the database as much
as possible, i.e. use native SQL joins wherever possible, native
conversion functions for transformations etc... By moving the majority
of processing logic to the database you can significantly increase
performance, because no ETL engine is transferring/converting data
faster than the datastore itself.
3) If the input data is really huge, maybe you should organize your
ETL process in a different manner, so that you query it only once, and
call other scripts inside this query.

[Anji]

Hi Fyodor,

Thank you very much for your suggestions.

With Thanks,
Anji.

[Rajesh Reddy Chejerla]

Hi,

I have written XML file like

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">

 <etl>

    <connection driver="script" id="js"/>

    <connection driver="auto" url="jdbc:mysql://localhost:3306/abcd" user="root" password="temp" id="db" /> 

<connection id="log" driver="text"/>

    

    <query connection-id="db">

        select count(loudbook_id) as c from lcs_al_keyphrase ; 

       

        <script connection-id="js">

             etl.globals['size1'] = 10 ;

        </script>

        

  </query>

 

  <script connection-id="js" if="etl.globals['size1'] gt 0">

        java.lang.System.out.println('errors count ='+etl.globals['size1']);

    </script>    

    

    

 </etl> 

i want to store the count in global variable but its given error

 javax.script.ScriptException: sun.org.mozilla.javascript.internal.EvaluatorException: Java class "java.util.HashMap" has no public instance field or method named "size1". (<Unknown Source>#2)

plz help me.

Thank you.

 

[Ivan Vasiliev]

    <connection driver="script" id="js"/>

I assume you need to use jexl instead of js or figure out proper syntax for js.

С уважением,
Васильев Иван

2012/10/30 Rajesh Reddy Chejerla <chraj...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "Scriptella ETL" group.

To view this discussion on the web visit https://groups.google.com/d/msg/scriptella/-/CJPYuG3qWjwJ.